Excel lookup interview question: match two tables

The answer interviewers want is =XLOOKUP(F2,$C$2:$C$5,$A$2:$A$5,"Not found"): find the ID in the staff table’s ID column and return the name from the column you need, with a fallback for an ID that isn’t there. In a file that must open in Excel 2019 or older, write the same lookup as =IFNA(INDEX($A$2:$A$5,MATCH(F2,$C$2:$C$5,0)),"Not found") (IFNA needs Excel 2013 or later; before that, use IFERROR). A plain VLOOKUP can’t answer this one, because the names sit to the left of the IDs.

The question

“Here’s an orders sheet and a staff list. Each order only has the staff ID of the rep who took it. Add a column to the orders that shows the rep’s name, and make it work when we paste in next month’s orders.”

It sounds like a quick task, and it is, once you’ve read both tables properly. Most candidates who stumble do so because they start typing before they’ve looked at which column holds what.

What they’re really testing

• That you read the data first. Which column is the key (the staff ID), where it sits in each table, and where the value you need back (the name) is.
• That you pick a lookup that fits. The IDs are in column C of the staff table and the names in column A, to their left. That rules out a plain VLOOKUP.
• That you match exactly. A lookup that quietly returns a neighbour’s name is worse than an error.
• That you plan for a missing ID. Real data always has one: a new hire, a typo, a leaver.

The worked answer

The staff list is in A1:C5, the orders in E1:G4. The formula goes in G2 and is copied down to G4:

G2fx=XLOOKUP(F2,$C$2:$C$5,$A$2:$A$5,"Not found")

Row	A	B	C	D	E	F	G
1	Name	Team	Staff ID		Order	Staff ID	Rep
2	Lena Hart	North	S-104		O-5531	S-117	Omar Diaz
3	Omar Diaz	South	S-117		O-5532	S-104	Lena Hart
4	Priya Shah	North	S-121		O-5533	S-140	Not found
5	Tom Okafor	West	S-130

That’s XLOOKUP. Read it out loud the way you would in the interview: “Find F2 in the staff IDs, C2 to C5, and return the matching name from A2 to A5. If it isn’t there, show Not found.” Order O-5531 was taken by S-117, Omar Diaz.

If the interviewer says the file goes to people on an older version of Excel, give the INDEX and MATCH version. MATCH finds the row of the ID in the list, and INDEX returns the name on that row:

The $ signs are what make it copy. Without them, the formula in G3 would search C3:C6, a range that starts below Lena Hart’s row, and order O-5532 would show “Not found” for a rep who is on the list:

Why VLOOKUP fails here

VLOOKUP searches the first column of the table you give it and can only return columns to its right. Give it A2:C5 and it searches the names, so no ID is ever found:

The old workarounds, moving the ID column to the left or building a helper column, change the client’s data to suit the formula. XLOOKUP and INDEX with MATCH take the search column and the return column separately, so neither needs it. (VLOOKUP over a table rebuilt with CHOOSE also works, but it’s harder to read and to check than either.) For the full comparison, see XLOOKUP vs VLOOKUP.

The follow-up: an ID that isn’t there

Order O-5533 has staff ID S-140, which isn’t in the list. With the fallback, G4 says so plainly:

The dangerous version is a lookup that doesn’t match exactly. MATCH without its last argument assumes the IDs are sorted and returns the closest one below. S-140 isn’t there, so it returns the row of S-130, and the order is credited to Tom Okafor with no error at all:

VLOOKUP without FALSE does the same. That’s why interviewers watch for the last argument: it’s the difference between an obvious gap and a quietly wrong commission report.

Common wrong answers, and why they fail

1. VLOOKUP on the whole table. It searches the names, not the IDs, and returns #N/A for every order.
2. MATCH or VLOOKUP without the exact-match flag. It works on the sample, then credits a missing ID to someone else.
3. No plan for a missing ID. A column full of #N/A in a report you hand on. Use XLOOKUP’s fourth argument or IFNA, and don’t use IFERROR, which also hides a broken formula (why #N/A appears covers the other causes).
4. Ranges that move when copied. Without $ signs, each row’s lookup searches a smaller list, and early rows go missing.
5. The wrong return column. Returning the team instead of the name. Read the header before you point at the range.
6. Typing the names in by hand. It answers today’s orders and none of next month’s. The interviewer said “make it work when we paste in new orders” for a reason.

Practise it: return the owner, or “Not found”

New staff, new IDs and a ticket table in place of the orders, so the formula above won’t fit as it is. We check the name your formula returns, then change the ID, including one that isn’t in the list.

Your task: In G2, return the name of the person whose ID is in F2, or the text "Not found" when that ID isn't in the staff list.

Graded live

G2fx

Row	A	B	C	D	E	F	G
1	Name	Dept	Emp ID		Ticket	Emp ID	Owner
2	Ana Kola	Finance	E-210		T-901	E-233
3	Ben Ortiz	Sales	E-224
4	Chloe Park	Ops	E-233
5	Dev Patel	Sales	E-247
6	Eva Novak	Finance	E-251

Write the formula in the bar above, then press Check or Enter.

Hint

Search the IDs in C2:C6 and return from the names in A2:A6. Give XLOOKUP "Not found" as its fourth argument, or wrap INDEX and MATCH (with 0) in IFNA.

Solution

=XLOOKUP(F2,C2:C6,A2:A6,"Not found") Any formula that returns the same result passes.

Check

How to answer it in the interview

Name the key, the column to search, the column to return and the missing case in one breath: “I’ll match on the staff ID, look it up in the ID column and return the name, exact match, with Not found for any ID that’s missing. XLOOKUP if we’re on Microsoft 365, INDEX and MATCH if the file has to work in older versions, with the ranges locked before I copy it down.” That sentence answers the follow-up questions before they come. More questions like this one: Excel interview questions.