LOOKUP is returning data for a lookup_value that isnt in the table

[U.Surender]

I have a pretty large table,(153,000 rows) My lookup_value is a 7 digit number. If my lookup_value isn't in the lookup_vector, it should return "#N/A" (which it does most of the time) but I noticed it was returning a result when the lookup_value didn't exist...it didn't exist anywhere in the table.

Please help...no idea why or how this is happening

 

[Jonmo1]

What's you're formula?

If you're using LOOKUP, then it's doing a "Closest Match" lookup, and requires the data to be sorted ascending.

 

[Jonmo1]

If you want to return #N/A if an exact match is not found, you need to use VLOOKUP instead of just LOOKUP
And use FALSE as the 4th argument.
And the data doesn't need to be sorted.

 

[U.Surender]

Makes sense!! Thanks guys!!