This is Sheet1 with Orders
Notice that there is the same Ref ID DEF456 for different Orders !
| Ref ID | Order Number |
|---|---|
| ABC123 | ORD001 |
| DEF456 | ORD002 |
| GHI789 | ORD003 |
| DEF456 | ORD004 |
| DEF456 | ORD005 |
In Sheet2
| Ref ID | Order Number | Status |
|---|---|---|
| ABC123 | ORD001 | success |
| DEF456 | ORD002 | success |
| GHI789 | ORD003 | fail |
| DEF456 | ORD004 | success |
| DEF456 | ORD005 | fail |
I need to return the Status for each order.
The problem is i can’t lookup a Column if the Ref ID & Order number are separated
I tried to combine the Column1 & Column2 but the problem is with the rang.
=VLOOKUP(CONCATENATE(A2,B2),Sheet2!,3,0)
=VLOOKUP(JOIN("",A2,b2),Sheet2!,3,0)
=XLOOKUP(AND(CONCATENATE(A2,B2),Sheet2!A2:A=A2:A,Sheet2!B2:B=B2:B),Sheet2!C2:C)
=INDEX(Sheet2!A2:C,MATCH(JOIN("",A2,B2),AND({Sheet2!A2:A}=A2,{Sheet2!B2:B}=B2),0),MATCH(JOIN("",A2,B2),Sheet2!1:1,0))