Formula to get
Sky wrote...
For 1 to 3 I could get the supplier A and B but for 4 and 5 is under
SupplierC and D I cannot get. *
....
LOOKUP *requires* that its lookup row or column be in ascending order.
It can fail when its lookup row or column is unsorted, as is the case
with your sample data.
Try
="Supplier "&INDEX(A$1:D$1,MATCH(E2,A2:D2,0))
|