The Pick the tool guide
The parts list has the supplier first and the part number second, and you have part numbers. Which lookup can return a column to the left of the one it searches? Pick one and try it.
Read the table first
You search the part numbers in column B and want the supplier from column A. VLOOKUP searches the first column of its table and returns a column to its right, so it can't do this without rearranging the table.
Two ways to write it
=XLOOKUP(E2, $B$2:$B$9, $A$2:$A$9)=INDEX($A$2:$A$9, MATCH(E2, $B$2:$B$9, 0))XLOOKUP takes the column to search and the column to return separately, in any order. INDEX/MATCH does the same in every version of Excel; MATCH needs 0 at the end for an exact match.
Where it goes wrong
- VLOOKUP. #N/A, or the wrong column.
- The price column. XLOOKUP returns whatever range you give it third.
- MATCH on its own. Returns the position, 8, not the supplier.
- No $. The ranges slide and the last part drops out.
The full lessons: XLOOKUP and INDEX and MATCH.
FAQ
- Can VLOOKUP look to the left?
- No. VLOOKUP searches the first column of its table and returns a column to the right. Use XLOOKUP or INDEX with MATCH.
- How do I look up a value to the left in Excel?
- Use =XLOOKUP(value, search_column, return_column), or =INDEX(return_column, MATCH(value, search_column, 0)).
- Why does my INDEX MATCH return the wrong row?
- MATCH is probably missing 0 as its last argument, so it looks for an approximate match in a list that isn't sorted.
