VLOOKUP problem

=VLOOKUP($P$3,$A$3:$B$100,2)

This formula is working but returning a result from 4 cells above the cell which should be returned. Why might this be? Thanks.

Dan

Good day,

Check for duplicate records across the A3:A100 range which match value in P3.

Show your appreciation - Press the star icon if this post has been helpful

It returns a result based on the first match it finds. It will not return results from subsequent matches.
But the fact that you have left out the FALSE (or 0) after the 2 means it will stop at a close match instead of an exact match.

Hi,

The VLOOKUP function can take an optional 4th argument that you don't seem to have used, range_lookup, which allows you to specify whether you want Excel to return an approximate or exact match. If you omit it and if there isn't an exact match Excel will return the next largest number.

To force Excel to return the exact value you require, insert ,FALSE after the 2 in your formula.

Or, if there is more than 1 exact match in a column, the function will return first one that it encounters "reading" from top to bottom.

Hope this helps

There aren't any duplicate records in the A3:A100 range. A3:A100 contains text formatted as text. The B3:B100 range contains Numbers formatted as numbers, where duplicates are present.

However, I have just turned the A3:B100 range into a table and now the correct result is being returned. Why is this?

To add 'FALSE (or 0) after the 2' would do this: =VLOOKUP($P$3,$A$3:$B$100,2,0) ? Thanks.

To add 'FALSE (or 0) after the 2' would do this: =VLOOKUP($P$3,$A$3:$B$100,2,0)

Yes, it tells Excel to find an exact match. If you leave out the 0 (or use a 1) it looks for a close match.

Sorry, correction - it was my previous sumifs formula that suddenly worked after making the range a table. VLOOKUP still seems to have problems so I have gone with =INDEX(B:B,MATCH(P3,A:A,0)). Thanks Fotis and thanks for everyone else's help.

Dan