Search code examples
excelexcel-formulaoffice365ms-officevlookup

Match two Columns and Pick corresponding value with DateTime validation

In Excel, I have a bunch of rows and columns as follows:

https://i.sstatic.net/9bSPH.png

Columns are marked A to H.

I want to pick the value in B, find it's corresponding value in G and put the value in column E.

The datetime value that matches in G should be the one that is greater than the corresponding datetime value in A.

Eg: B2=AMARJABAT corresponding value in column G is at G10 and G10 > A2.

I tried adding this formula in E but it doesn't work as expected. What do I need to change or use VLOOKUP?

=INDEX($B$2:$B$388, MATCH(H2,$H$2:$H$344))

Added example for more clarity:

I want to populate column "E". For that, match column 'B' value with corresponding column 'H'.

For eg: Look at B2 (AMARJABAT). Search for it in Column 'H', Pick the adjacent datetime which here is G10 and populate this datetime value E2.

Similarly look at B3 (LCHSGFIN). Search for it in Column 'H'. Pick the adjacent datetime which here is G20 and populate this datetime value E3.

and so on.

The datetime value that matches in G should be the one that is greater than the corresponding datetime value in A.

Solution

  • With Excel 2019+ or Office O365

    =MINIFS($G$2:$G$100,$H$2:$H$100,B2,$G$2:$G$100,">" & A2)

    Edit: Missed a set of parentheses If you have an earlier version of Excel, try:

    =AGGREGATE(15,6,1/(($H$2:$H$100=B2)*($G$2:$G$100>A2))* $G$2:$G$100,1)

    Both formulas will return #NUM! error if no match found.