Vlookup with specific date showing N/A-CodePudding

I am trying to apply vllokup for a specific month of a specific year. My formula works for string lookup value but not works for date. here I attached my effort

CodePudding user response：

First of all VLOOKUP() will not work in this case because VLOOKUP() always lookup values on first column of table then return value from corresponding row from specified column. So, you first column is strings and vlookup do not find dates on first column. Use Index/Match instead.

=INDEX(B3:B5,MATCH(D1,C3:C5,0))

You can also use FILTER() function.

=FILTER(B3:B5,C3:C5=D1)

Query() will also work-

=QUERY(B3:C,"select B where C= date '" & text(D1,"yyyy-mm-dd") & "'")

CodePudding user response：

Vlookup works by searching columns to the left of the searched column. So you should modify your sheet to look like this (if possible) :

However, you should also take into account that the formatting of your cells could be misleading. If your D4cell has the value 1/Jan/2022 and your C6 cell has the value of 2/Jan/2022, then both of them will show as Jan 2022, but your formula will still return N/A because the real values of the cells are different.