How to / Lookups

How to look up a value to the left in Excel

VLOOKUP cannot look left. Use =XLOOKUP("P-2", B2:B4, A2:A4): it takes the column to search and the column to return separately.

The name for a code, when the names are on the left

ABCD
1NameCode
2AnaP-1Ben
3BenP-2
4ChloeP-3

In D2: =XLOOKUP("P-2", B2:B4, A2:A4) → Ben

How it works

  1. The first range, B2:B4, is where XLOOKUP searches for the code.
  2. The second range, A2:A4, is where the answer comes from.
  3. The two can be in any order, on either side of each other.

Variations

  1. In older versions of Excel

    ABCD
    1NameCode
    2AnaP-1Ben
    3BenP-2
    4ChloeP-3

    In D2: =INDEX(A2:A4, MATCH("P-2", B2:B4, 0)) → Ben

    MATCH finds the row of the code; INDEX returns the name on that row.

  2. Your own text when the code is missing

    ABCD
    1NameCode
    2AnaP-1Not found
    3BenP-2
    4ChloeP-3

    In D2: =XLOOKUP("P-9", B2:B4, A2:A4, "Not found") → Not found

  3. What VLOOKUP does with the same table

    ABCD
    1NameCode
    2AnaP-1#N/A
    3BenP-2
    4ChloeP-3

    In D2: =VLOOKUP("P-2", A2:B4, 1, FALSE) → #N/A

    It searches column A, the first column of the table, where there are no codes.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related