Excel Formula to Lookup Salary by First and Last Name

Managing payroll spreadsheets often becomes a frustrating struggle when first and last names are split across separate columns, rendering standard lookup tools ineffective. Traditionally, professionals rely on manual sorting or tedious helper columns to bridge this data gap. Fortunately, deploying advanced multi-criteria lookup formulas grants you the ability to retrieve salary figures with absolute precision and zero structural changes to your sheet. Note the stipulation: exact spelling and spacing are required to avoid formula errors. For example, the formula =XLOOKUP(A2&B2, D:D&E:E, F:F) instantly solves this. Next, we will explore how to configure this step-by-step.

In the world of data analysis and spreadsheet management, matching records based on a single criterion is straightforward. If you need to find a salary based on an Employee ID, a simple VLOOKUP or INDEX/MATCH does the trick. However, real-world data is rarely that convenient. Quite often, you are handed a list of employees with their first and last names split into separate columns, and you need to match both fields simultaneously to retrieve their correct salary.

Using a standard VLOOKUP here will fail because it only searches for a single value in the leftmost column. If you search only by "John", you might pull the salary for "John Smith" instead of "John Doe". If you search only by "Doe", you risk matching "Jane Doe". To solve this, you need a multi-criteria lookup. In this comprehensive guide, we will explore the four best ways to match both First Name and Last Name to retrieve a Salary in Excel, ranging from modern functions to legacy compatibility tricks.

The Sample Dataset

To follow along with these examples, let's assume we have the following master employee database (columns A to C) and a lookup table where we want to populate the salaries (columns E to G):

We want to find the salary for John Doe. If we only search for "John", we might incorrectly get $65,000 (John Smith). If we search for both, we should correctly get $74,000.

Method 1: The Modern & Elegant Way (XLOOKUP)

If you are using Excel 365, Excel 2021, or Excel for the Web, XLOOKUP is the absolute best tool for this job. It is cleaner, faster, and does not require pressing special keyboard shortcuts to handle array logic.

There are two ways to write an XLOOKUP for multiple criteria: using concatenation or using Boolean logic.

Approach A: Concatenation (&)

This approach joins the First Name and Last Name together with an ampersand (&) in both the lookup value and the lookup arrays.

=XLOOKUP(E2 & F2, A2:A6 & B2:B6, C2:C6)

How it works:

• E2 & F2: Glues the lookup first name ("John") and last name ("Doe") together to form the search string "JohnDoe".
• A2:A6 & B2:B6: Glues the columns of first and last names together in memory, creating a temporary array: {"JohnSmith"; "JaneDoe"; "JohnDoe"; "MichaelBrown"; "EmilyDavis"}.
• C2:C6: The return array containing the salaries.
• XLOOKUP searches for "JohnDoe" in the temporary array, finds it at index 3, and returns the 3rd value from the salary column ($74,000).

Approach B: Boolean Logic (Highly Recommended)

For large datasets, concatenation can sometimes slow down Excel because it creates temporary text strings in memory. The Boolean logic method is faster and mathematically elegant:

=XLOOKUP(1, (A2:A6=E2) * (B2:B6=F2), C2:C6)

How it works:

• (A2:A6=E2) evaluates each cell in column A against "John", returning an array of TRUE/FALSE: {TRUE; FALSE; TRUE; FALSE; FALSE}.
• (B2:B6=F2) evaluates each cell in column B against "Doe", returning: {FALSE; TRUE; TRUE; FALSE; FALSE}.
• The multiplication sign (*) acts as an AND operator. In Excel arithmetic, TRUE = 1 and FALSE = 0. Multiplying these arrays yields:
  {1*0; 0*1; 1*1; 0*0; 0*0} which simplifies to {0; 0; 1; 0; 0}.
• XLOOKUP then searches for the number 1 in that resulting array, finds it at position 3, and returns the 3rd salary ($74,000).

Method 2: The Classic Powerhouse (INDEX & MATCH)

If you are working on a version of Excel older than 2021 (like Excel 2019, 2016, or 2013), you won't have access to XLOOKUP. The industry standard for these versions is the dynamic duo: INDEX and MATCH.

Just like XLOOKUP, we can construct this using Boolean array multiplication:

=INDEX(C2:C6, MATCH(1, (A2:A6=E2) * (B2:B6=F2), 0))

Important Note for Older Excel Versions: If you are using Excel 2019 or earlier, this is an array formula. After typing this formula into the cell, do not just press Enter. You must press Ctrl + Shift + Enter. Excel will automatically wrap your formula in curly braces { } to indicate it is executing array logic.

How it works:

• The MATCH function looks for the value 1 in the array created by multiplying the two criteria arrays.
• It returns the relative row position (which is 3).
• The INDEX function then looks at the range C2:C6 and pulls the value from the 3rd row ($74,000).

Method 3: The No-Formula-Stress Route (Helper Column)

If you find array formulas or XLOOKUP syntax confusing, or if your workbook is running slow and you want to keep calculations basic, the Helper Column is your best friend. It is incredibly reliable and works in every version of Excel ever made.

Step 1: Create a Helper Column

Insert a new column to the left of your salary data. Let's say we insert a new Column C, making "Salary" move to Column D.

In cell C2, write a formula to combine the first and last name, separated by an underscore (to prevent accidental mismatches like "Joh Ndoe" matching "John Doe"):

=A2 & "_" & B2

Drag this formula down. Column C will now display: John_Smith, Jane_Doe, John_Doe, etc.

Step 2: Use standard VLOOKUP

Now that you have a single unique identifier, you can use a basic, non-array VLOOKUP formula to pull the salary:

=VLOOKUP(E2 & "_" & F2, C2:D6, 2, FALSE)

This looks for "John_Doe" in your helper column and safely extracts the salary from the adjacent column.

Method 4: The Numeric Trick (SUMIFS)

If your ultimate goal is to retrieve a numeric value (like Salary, Sales, or Age) and you are certain there are no exact duplicate names in your list, you can bypass lookup formulas entirely and use SUMIFS.

=SUMIFS(C2:C6, A2:A6, E2, B2:B6, F2)

How it works:

SUMIFS calculates the sum of cells that meet multiple criteria. Since there is only one "John Doe" in our dataset, summing his salary returns exactly his salary ($74,000).

Pros:

• Extremely fast computation time on massive worksheets.
• No array formulas or complex lookup logic required.
• Returns 0 instead of an ugly #N/A error if the employee is not found.

Cons:

• Only works for numbers. If you wanted to match first and last names to look up their Department or Job Title (text fields), SUMIFS will return 0.
• If "John Doe" is listed twice in the sheet, it will sum both salaries together, giving you an incorrect inflated number.

Summary: Which Method Should You Use?

To help you choose the right formula for your specific spreadsheet, refer to this quick comparison table:

By mastering these four techniques, you can easily tackle multi-column lookups in Excel and ensure your payroll, HR, or financial dashboards always match salaries to the right people every time.