Advanced Lookup Techniques Beyond VLOOKUP

Excel offers extensive lookup techniques for data management and data analysis. VLOOKUP is commonly used for data retrieval, but it has limitations like needing the lookup column on the left and inflexible error handling. To overcome these, advanced functions like XLOOKUP and INDEX-MATCH-MATCH provide more flexibility, control, and efficiency. Let’s show you the advanced lookup techniques beyond VLOOKUP by using XLOOKUP, and INDEX-MATCH-MATCH to leverage them effectively.

Let’s consider a sales dataset to apply advanced lookup techniques by using practical examples.

XLOOKUP Advanced Lookup Techniques

XLOOKUP is a versatile function in Excel for single- or multiple-column lookups. It can search in any direction (left-to-right, right-to-left, vertical, horizontal), offers custom error messages, and doesn’t require sorted data for lookup. The XlOOKUP function is only available for Excel 2021 and MS Office 365 users. XLOOKUP function auto updates data changes of lookup values.

Syntax:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value to search for.
• lookup_array: The range or array to search within.
• return_array: The range or array from which it will return the result.
• [if_not_found]; [Optional]: The value to return if no match is found.
• [match_mode]; [Optional]: Specify the type of match. You can choose exact, wildcard, or approximate matching.
  • 0  – Exact match (default).
  • 1  – Exact or next larger.
  • -1 – Exact or next smaller.
  • 2  – Wildcard match.
• [search_mode]; [Optional]: Decide the search direction, such as first-to-last or last-to-first
  • 1 – Search from first to last.
  • -1 – Search from last to first.
  • 2 – Binary search (ascending order).
  • -2 – Binary search (descending order).

1. Using XLOOKUP for Single Criteria

Let’s find the closest sales amount of $100 from our sales dataset to look at the customer who spends nearly $100 on any order. Insert the following formula.

Formula:

=XLOOKUP(100, G2:G71, A2:G71, “Not Found”, 1)

This formula searches for 100 in the range G2:G71 and returns the corresponding row from A2:G71. If the closest of 100 isn’t found, it will display “Not Found”.

Output:

1007     1/4/2024            Daniel Martinez             East      39.99    3             119.97

2. Using XLOOKUP with Multiple Criteria

You can perform more complex lookups by concatenating multiple criteria using XLOOKUP. Let’s explore the formula.

Formula:

=XLOOKUP(“Melissa Lopez” & “West”, C2:C71 & D2:D71, A2:G71)

This formula concatenates the customer’s name and region and looks for the combined value within the concatenated lookup arrays. It retrieves the corresponding from the selected range.

Output:

1012     1/6/2024             Melissa Lopez  West     79.99    2             159.98

INDEX-MATCH-MATCH for Advanced Lookup Techniques

INDEX-MATCH-MATCH is used when you need to look up values based on both row and column criteria, ideal for 2D data tables.

Syntax:

=INDEX(array, MATCH(row_lookup_value, row_lookup_array, 0), =MATCH(column_lookup_value, column_lookup_array, 0))

• array: The cell range contains the values you want to retrieve.
• MATCH(row_lookup_value, row_lookup_array, 0): Returns the row number.
  • row_lookup_value: The value to search for in the rows.
  • row_lookup_array: The row ranges to search within.
  • 0 – Exact match

• MATCH(column_lookup_value, column_lookup_array, 0): Returns the column number.
  • column_lookup_value: The value to search for in the columns.
  • column_lookup_array: The column ranges to search within.
  • 0 – Exact match

1. 2D Lookups for Rows and Columns

Let’s look at the sales amount for a customer of a particular sales amount by using the INDEX-MATCH-MATCH formula. Insert the following formula.

=INDEX(A2:G71, MATCH(“John Smith”, C2:C71, 0), MATCH(“Sales Amount”, A1:G1, 0))

This formula searches for John Smith in the range C2:C71 and “Sales Amount” in A1:G1. Then it will return to the intersecting value in A2:G71.

Output:

99.98

2. Advanced INDEX-MATCH-MATCH Formula for 3D Lookup

Since INDEX-MATCH-MATCH is typically for two-dimensional lookups, you can extend it to a three-dimensional lookup by using an array formula with multiple MATCH functions. It’s ideal for large datasets and complex criteria matching, outperforming VLOOKUP in flexibility and capability.

Formula:

=INDEX(G2:G71, MATCH(1, (C2:C71=”John Smith”) * (D2:D71=”South”) * (F2:F71=4), 0))

This formula searches for the Sales Amount by matching three criteria: Customer Name in column C, Region in column D, and Quantity in column F. The conditions are combined using multiplication as an AND function, allowing MATCH to find the row where all criteria are met, and INDEX returns the corresponding value from G2:G71.

Output:

129.99

Advantages of Using XLOOKUP and INDEX-MATCH

Why Choose XLOOKUP over VLOOKUP?

• Bidirectional Search: XLOOKUP can look up values in both directions, while VLOOKUP can only search left to right.
• No Column Index Number: No need to specify the column index number so the change of columns won’t affect it.
• Default Exact Match: By default, XLOOKUP searches for an exact match, minimizing errors.
• Wildcard Searches in XLOOKUP: You can use wildcards like * and? with XLOOKUP when performing text searches.
• Error Handling: You can mention what to return if no match is found.

Benefits of INDEX-MATCH over VLOOKUP

• Flexible Table Structure: INDEX-MATCH is unaffected by column insertions or deletions.
• Left Lookup: Unlike VLOOKUP, INDEX-MATCH can look to the left.
• Performance: More efficient in large datasets as MATCH is faster in some cases compared to recalculating VLOOKUP.

Conclusion

Advanced lookup techniques like XLOOKUP and INDEX-MATCH-MATCH are essential for advanced data handling in Excel. These functions automatically update data changes. These functions provide flexibility, accuracy, and performance that surpass traditional VLOOKUP, making them crucial for anyone looking to level up their Excel skills.