CODEWITHSUNDEEP
excelexcel-formula
donviti
donviti

Reputation: 294

match index getting #n/a when Vlookup produces a result

I can run a vlookup on something and can get an answer. When I go to run it as an index/match I get #N/A

I'm doing an index on a date. I go to see if the dates are equal and they are not. I don't know how to massage the dates to get them to match? They look the exact same. I've tried to format them the exact same to no avail.

Can someone give me advice? I've already tried adding +0 to B26 and that didn't work.

=INDEX('sheet2'!B13:B20,MATCH(B26,'sheet2'!D13:D20,0))

Why am I switching to index/match when my vlookup works fine? Because the dates I'm searching on will be in the same month in year but not exact day. I thought I would use index/match to ultimately find the month/year and pull in the data that way.

Upvotes: 0

Views: 358

Answers (1)

Yuri Kopylovski
Yuri Kopylovski

Reputation: 380

You could create a new column with a string made up of parts of the date and MATCH based on that. It's possible that you are not using VLOOKUP with the last parameter set to "false", so it is giving you the nearest result, not an exact match, and when you switch to index/match, it's no longer finding the nearest value.

For example, you could have the column (let's say E) contain

=YEAR(B13)&"-"&MONTH(B13)

And then use:

=INDEX('sheet2'!B13:B20,MATCH(YEAR(B26)&"-"&MONTH(B26),'sheet2'!E13:E20,0))

Upvotes: 1

Related Questions