Vlookup and If function

[Eric Tsang]

Dear Expert

I got a problem with Vlookup and If function


I have attached the excel file that list out product with price on the date , and purchase on the other day
the price will be varied according to the date of purchase, how can i get the right Price with IF and Vlookup formular or some better way?


Thanks

[Special-K]

The price in F4 is wrong, it should be 12.

B8 also has a space behind Car 2 which should be removed.

in F3
=INDEX(C3:C8,MATCH(D3,IF(E3=B3:B8,A3:A8),1),1)
and copy down

Array formula, use Ctrl-Shift-Enter

[Phuocam]

Try this ...

=INDEX($C$3:$C$8,MATCH(1,(E3=$B$3:$B$8)*(D3>=$A$3:$A$8),0))

Enter with Ctrl+Shift+Enter.

[JohnTopley]

Doesn't work: you need either in separate table for each car OR Car 1 /dates followed by Car 2/dates with dates in ascending order in each case

Try

=IFERROR(INDEX($B$11:$F$14,MATCH($D3,INDEX($B$11:$F$14,,MATCH($E3,$B$10:$F$10,0)),1),MATCH($E3,$B$10:$F$10,1)+1),"")

OR

=IFERROR(VLOOKUP($D3,OFFSET($A$16,MATCH($E3,$B$16:$B$21,0)-1,,COUNTIF($B$16:$B$21,$E3),3),3,1),"")

uses new tables see attached.