CODEWITHSUNDEEP
excelexcel-formula
Talia
Talia

Reputation: 13

Excel V-LOOKUP on two columns with closest match on one column

I have rate table like the following: until date = the date when this rate expired

| code   | until_date |  rate   |
---------------------------------
|   1    |            |   100   |
|   2    | 01/01/2020 |   150   |
|   2    |            |   200   |
|   3    |            |   300   |

On another sheet, I have code and date, and need to find the rate that match the code and not passed the expire date (if there is value in that column)

| code   |     date   |   rate  |
---------------------------------
|   2    | 01/10/2019 |   ???   |  >> need to get the value 150
|   2    | 01/10/2020 |   ???   |  >> need to get the value 200
---------------------------------

Already tried to use vlookup() with condition - didn't work Also tried to use match() but it lookup in one column only

Please help if you have any idea how to solve this issue. I can use today() function in the empty blank date cells if it helps

Upvotes: 1

Views: 120

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Use INDEX/AGGREGATE:

=INDEX(C:C,AGGREGATE(15,7,ROW($C$2:$C$5)/(($A$2:$A$5=F2)*(G2<IF($B$2:$B$5="",NOW(),$B$2:$B$5))),1))

Depending on one's version this may require the use of Ctrl-Shift-Enter instead of Enter when exiting edit mode.

enter image description here

Upvotes: 1

Related Questions