Lookups on two or more columns

1 / 6

Find each person's extension. There's more than one Tariq, so the first name alone isn't enough.

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

Tariq

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How XLOOKUP works

When do you need two columns?

When one column isn't enough to tell rows apart. Two people called Priya, or two called Patel: only the first name and the last name together point to one person. The same goes for a product and a size, or a date and a store.
How do you look up on two columns?

Join them with & on both sides. Join the two values you are looking for, and join the two columns you search, in the same order:
=XLOOKUP(E2&F2,$A$2:$A$11&$B$2:$B$11,$C$2:$C$11)
$A$2:$A$11&$B$2:$B$11 builds a list of joined names (PriyaPatel, PriyaLi...) for XLOOKUP to search, without adding a column to the sheet.
Should I put a space in between?

It's safer: E2&" "&F2 and $A$2:$A$11&" "&$B$2:$B$11. Without one, "Ann" + "Ewing" and "Anne" + "Wing" both become AnnEwing.
What about older Excel?

INDEX and MATCH work the same way: =INDEX($C$2:$C$11,MATCH(E2&F2,$A$2:$A$11&$B$2:$B$11,0)). Before Excel 365 it had to be entered with Ctrl+Shift+Enter. Another classic fix is a helper column with =A2&B2 that VLOOKUP can search.

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