Lookups
INDEX and MATCH: look up a column to the left of the search key
VLOOKUP can only search the leftmost column of a range. Combine INDEX and exact MATCH when the identifier you have sits to the right of the value you need to retrieve.
When the lookup key is not in column A
A plumbing supply room prints shelf labels ordered by warehouse bin in column A, part description in column B, and SKU in column C. A picker scanning SKU-730 needs the bin location in column A without reordering columns that existing print templates depend on.
| Bin (A) | Part (B) | SKU (C) |
|---|---|---|
| Aisle 1B | Copper elbow | SKU-410 |
| Aisle 2A | Brass valve | SKU-205 |
| Aisle 2D | PVC coupling | SKU-730 |
| Aisle 3C | Drain trap | SKU-118 |
| Aisle 4A | Seal ring | SKU-592 |
Pair MATCH for the row position with INDEX for the return column
Put SKU-730 in E2. MATCH(E2, C2:C6, 0) scans C2:C6 for an exact match (0) and returns 3 because SKU-730 is the third item inside that five-row range. INDEX(A2:A6, 3) then reads the third item from the bin range A2:A6, returning Aisle 2D.
=INDEX(A2:A6,MATCH(E2,C2:C6,0))Why VLOOKUP fails and why the 0 in MATCH is mandatory
Writing =VLOOKUP(E2, A2:C6, 1, FALSE) searches column A (bins) for SKU-730 and immediately fails with #N/A. Inserting or deleting a description column between A and C also leaves INDEX/MATCH intact because it references A2:A6 and C2:C6 directly rather than counting columns.
Always include 0 as the third argument of MATCH for identifiers. Notice that SKU-410, SKU-205, and SKU-730 are not in alphabetical order. Omitting 0 tells MATCH to assume ascending order, which can return the wrong shelf or a false #N/A.
Keep both ranges aligned on the same start and end rows
MATCH returns a relative position inside C2:C6, not a worksheet row number. In our table SKU-730 sits on worksheet row 4, which is position 3 of C2:C6. If you accidentally wrote INDEX(A1:A6, MATCH(E2, C2:C6, 0)), position 3 of A1:A6 would read row 3 (Aisle 2A) — one row too high.
Guard missing SKUs explicitly with ISNA on MATCH
When E2 holds an unknown code such as SKU-999, =MATCH(E2, C2:C6, 0) returns #N/A. Checking ISNA(MATCH(...)) before calling INDEX displays "SKU not found" cleanly across legacy Excel, modern Excel, and Arte Sheets. Change E2 to SKU-118 to verify Aisle 3C (position 4), then test SKU-999.
=IF(ISNA(MATCH(E2,C2:C6,0)),"SKU not found",INDEX(A2:A6,MATCH(E2,C2:C6,0)))Try the example
Use the original values or change them to test your understanding.