Looking left with INDEX and MATCH

1 / 5

Find the name for each staff ID in E2:E6. The IDs are in column C, to the right of the names.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEFG
1NameDepartmentStaff IDStaff IDName
2Mateo WalshSalesS1530S1300
3Isla GarciaSalesS1880S1250
4Zoe OkaforITS1400S1690
5Amara SilvaHRS1640S1070
6Priya BrennanFinanceS1690S1530
7Victor FischerOperationsS1250
8Uma SilvaFinanceS1890
9Nadia PatelOperationsS1090
10Hiro PatelOperationsS1300
11Rosa EvansHRS1070
12
13
14
15
IDs
names
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How INDEX and MATCH works

  • Why can't VLOOKUP look left?

    VLOOKUP always searches the first column of its table and can only return columns to the right of it. When the column you search is on the right, such as an ID in column C and names in column A, VLOOKUP can't reach the names.

  • How do INDEX and MATCH fix it?

    Split the lookup into two steps:

    • MATCH finds the position of the ID in its column.
    • INDEX takes the value at that position from any other column.

    =INDEX($A$2:$A$11,MATCH(E2,$C$2:$C$11,0))

  • What has to line up?

    Both ranges must start on the same row and be the same size, so that position 3 in one is the same row as position 3 in the other. Start both at row 2, under the headers.

  • Why learn it if XLOOKUP exists?

    XLOOKUP does the same in one function, but it is only in Excel 2021 and newer. INDEX and MATCH work in every version of Excel and in Google Sheets, so you will meet them in older workbooks all the time.

Know someone fighting with a spreadsheet? Send them this page.