VLOOKUP FAILS ON dd/mm/yyyy hh:mm FORMAT

[mariec_06]

in the attached table, cell C5 should return the relevant value of A1:A3 if B5 equals any value from B1:B3.

All date are formatted as "dd/mm/yyyy hh:mm" and i tried to round it too without any success.

Can someone give me the trick?

[FerdyHar]

Hey there,

The problem is not related to the time format, it is the fact that you have the value you are looking up in the second column, Vlookup doesn't like this. The quick fix is swapping the 3 dates in the b column with the 3 activities in the a column. Otherwise you might want to use another formula, but not Vlookup.

The workbook is attached with the quick fix.

Ferdy

[samba_ravi]

try
=VLOOKUP(B5,CHOOSE({1,2},B1:B3,A1:A3),2,FALSE) or
=INDEX(A1:A3,MATCH(B5,B1:B3,0))

in VLookup lookup value should always be in the first column in the range (Table_array)
in your case it lookup value is in second column of the selected range, thats why it is giving an error value

[Phuocam]

Try this:

=INDEX($A$1:$A$3,MATCH(B5,$B$1:$B$3,0))

[mariec_06]

found it

=INDEX(A1:A3,MATCH(B5,B1:B3))