Reference for how Excel functions VLOOKUP, XLOOKUP, and INDEX(MATCH) work.

VLOOKUP, XLOOKUP, and INDEX-MATCH.md

VLOOKUP, XLOOKUP, and INDEX/MATCH

Example: Looking up a SKU to return a price.

Note that C is the the column where the SKU is located in, and H is the end of the column. The price is located in column G, so 5 columns away from C.

' Where A2 is the SKU you're looking up in the current sheet and SKU_Price_Table is a Table in another sheet '
=VLOOKUP(A2, SKU_Prices_Table!C:H, 5, FALSE)

Note that if you have numbers and text mixed together, VLOOKUP will only return the matching text columns (e.g. a SKU that is just 123 and a SKU that is F-123, VLOOKUP will return N/A for 123 even if there is a match).

You can work around this by either:

1. Formatting both your columns to be text
2. Wrapping your VLOOKUP value in TEXT()

e.g.

=VLOOKUP(TEXT(A2, 0), SKU_Prices_Table!C:H, 5, FALSE)

XLOOKUP and INDEX/MATCH EQUIVALENTS TO ABOVE

XLOOKUP

' where C is your lookup column, G is your return column, "NO MATCH" is what you want to return when there's no match, and 0 and 1 are match & search mode'
=XLOOKUP(A2, SKU_Prices_Table!C:C, SKU_Prices_Table!G:G, "NO MATCH", 0, 1)

INDEX/MATCH

' Where C is your lookup column, G is your return column, and 0 is your match type'
=INDEX(SKU_Prices_Table!G:G, MATCH(A2,  SKU_Prices_Table!C:C, 0))