Excel XLookUp with single critera but multiple columns
Categories:
Excel XLOOKUP with Single Criteria and Multiple Columns
Discover how to leverage Excel's powerful XLOOKUP function to retrieve data from multiple columns based on a single lookup criterion, enhancing your data analysis capabilities.
XLOOKUP is a modern and flexible replacement for older lookup functions like VLOOKUP and HLOOKUP in Excel. While commonly used for single-column lookups, its true power shines when you need to return values from multiple columns simultaneously based on just one matching criterion. This article will guide you through the syntax and practical applications of this powerful feature.
Understanding the XLOOKUP Syntax
Before diving into multi-column returns, let's briefly review the basic XLOOKUP syntax. The function takes several arguments, but for our purpose, the first three are crucial:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value: The value you are searching for.lookup_array: The range where you expect to find thelookup_value.return_array: The range containing the values you want to return. This is where the multi-column magic happens.
Retrieving Multiple Columns with a Single Criterion
To return values from multiple columns, the key is to define the return_array as a range that spans all the desired columns. XLOOKUP will then return an array of values, spilling them into adjacent cells if there's enough space. This eliminates the need for multiple XLOOKUP functions or complex array formulas.
=XLOOKUP(A2, C2:C10, D2:F10)
This formula looks for the value in A2 within the range C2:C10. Once found, it returns the corresponding values from the range D2:F10. The results will spill into three adjacent cells to the right of the formula cell.
XLOOKUP returning multiple columns from a single lookup
Handling 'Not Found' Scenarios
Just like single-column lookups, you can specify what XLOOKUP should return if the lookup_value is not found. This is done using the optional if_not_found argument. It's good practice to include this to make your formulas more robust and user-friendly.
=XLOOKUP(A2, C2:C10, D2:F10, "Not Found")
If the value in A2 is not found in C2:C10, the formula will return 'Not Found' in the first cell, and #N/A for the subsequent spilled columns. To return 'Not Found' for all columns, you can use an array constant, e.g., {"Not Found", "", ""} or a more dynamic approach.
if_not_found argument with multi-column returns, a simple string like "Not Found" will only apply to the first returned column. If you want a specific message for all columns or an empty string, you might need to construct an array for the if_not_found argument, e.g., {"Item Not Found", "", ""}.Practical Application: Employee Data Lookup
Imagine you have a list of employees with their Employee ID, Name, Department, and Email. You want to quickly find an employee's Name, Department, and Email by entering only their Employee ID.
1. Step 1
Organize your data: Ensure your employee data is in a table or range, for example, Employee ID in column A, Name in B, Department in C, and Email in D.
2. Step 2
Choose a lookup cell: Designate a cell (e.g., F2) where you will enter the Employee ID you want to look up.
3. Step 3
Enter the XLOOKUP formula: In the cell where you want the first result (e.g., G2), type the formula: =XLOOKUP(F2, A2:A100, B2:D100, "Employee Not Found"). Adjust ranges A2:A100 and B2:D100 to match your actual data range.
4. Step 4
Observe the spill: XLOOKUP will return the Name, Department, and Email in G2, H2, and I2 respectively, spilling automatically.
#SPILL! error.