Lookup give a wrong value, how could I fix it?

[alice9527]

Hi guys, trying to fix this issue. The formulas were written by somebody else.

This is my formula:
=IFERROR(LOOKUP('QCP Review Data'!H10,'Group list'!A:A,'Group list'!B:B),"")

Data in Group list A:A are different test codes, and B:B are several groups.

So theoretically, the formula should be looking for which group the test code belongs to when a test code is entered.

But don't know why some of them are giving wrong results.

Magically half-fixed the issue by moving rows in the "Group list"

Photo 1 is part of the original sheet. When test codes "W0200-15" and "W0202-15" are entered, excel gives me "Enviro" rather than "Fats"

Photo 2 is the half-fixed sheet, which fixed the previous issue, but when entering " W0800-15" and "W3100-15", excel gives me "Powders" rather than "Enviro"

I've tried to sort all data in the "Group list" by alphabetical order, but everything goes wrong with that.

Really appreciate if anyone have an idea why it goes wrong and how could it be fixed.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=XLOOKUP(H10,'Group list'!A:A,'Group list'!B:B,"",0)

 

[alice9527]

It works! Thank you so much!

But I don't want the cell show "0" when H10 is empty, so I made it looks like this

=IF(H10="","",XLOOKUP(H10,'Group list'!A:A,'Group list'!B:B,"",0))

Is that alright?

 

[Fluff]

Glad to help & thanks for the feedback.