I am working with 2 excel sheets (same documents). In sheet 1, I have a list of Cars IDs (Column A), and in sheet 2 I have the same list of car's ID (Column A) and the car's name (Column B).
I use lookup to copy the cell's value of the car's name (Sheet 2, column b) into Column E in sheet 1 where the car's id (column A in both sheets) are identical in both sheets.
When I enter the formula, excel returns all the correct car's names when both car's ID in both columns match each other. However, for some known reason rooted in lookup function, when Excel can't find a car from sheet 1 in sheet 2 instead of not returning any data, it returns a data value that doesn't match the criteria.
Same problem, actual data:
Here I have the same problem, I have a search ID (column A) and lookup cell value excel should return if search id in sheet 1 matches the search id in sheet 2.
The cell marked in red doesn't exist in the sheet 2.
What actually happens that lookup function returns all the correct results when the criteria I set in the function is met. However, lookup also returns the wrong results when the criteria is not met - like in the red cell in the link where the search id doesn't even exist in the other sheet but excel still returns a result.
I understand there's a problem with lookup when Lookup finds a value that's greater than the lookup value, it will fall back, and match a previous value.
Is there any way to fix this issue or to use another function\functions that returns the correct data when the criteria is met and doesn't return data when the criteria is not met.
LOOKUPformula. UseVLOOKUP()with the forth criterion asFALSE– Scott Craneriferror,match(lookup_value= 0), andindexfor excel. – Riley Carney