Two-Dimensional Lookup

Enhanced Efficiency; Cleaner Worksheets

By Ng Xian Hui (founder of Backbone)

Originally posted on Chartered Accountants Lab

TAKEAWAYS

• VLOOKUP + MATCH is the best approach for two-dimensional lookups in Excel.
• MATCH makes VLOOKUP dynamic, eliminating manual column adjustments.
• Nesting improves clarity, but excessive nesting reduces readability.
• XLOOKUP requires a column reference, making it less efficient here.

In my previous article which compared XLOOKUP and VLOOKUP, I mentioned that XLOOKUP is generally more efficient, except for two-dimensional lookups. But what exactly does that mean?

THE PROBLEM

Many of our data tables in accounting are two-dimensional, which makes them more readable. For example, if we have a pricing table featuring products and countries, accountants would generally prefer the format of Table 1 over Table 2:

Table 1

Table 2

While Table 1, a two-dimensional table, is more human-friendly, it’s not ideal for Excel’s lookup functions. If you prefer not to unpivot the table but still want to retrieve some data points automatically using lookup functions, continue reading.

Table 3

USING VLOOKUP

Let’s see how to use VLOOKUP to find the price of IA101 for Singapore.

Understanding VLOOKUP

The VLOOKUP function helps find values in a table. It has four arguments:

1. Lookup value: the value to search for
2. Table array: the range of data to search within
3. Column index number: the column from which to return the value
4. Range lookup: use 0 or FALSE for an exact match

VLOOKUP is the function we need.

Table 4

However, no one likes to make a change to the formula in cell B10 every time the country changes. So, we need a dynamic column index number (argument 3) instead of a static one. But how do we make it dynamic?

MATCH

The best companion for VLOOKUP:

MATCH helps determine the relative position of a lookup value within a given range. Unlike VLOOKUP, it does not return the actual value but its position, which is exactly what we need to dynamically select the correct column.

MATCH function structure:

=MATCH (lookup_value, lookup_array, match_type)

• Lookup value: the value to search for (example, the country name)
• Lookup array: the range containing column headers (example, a row of country names)
• Match type: use 0 or FALSE for an exact match

We use the MATCH function to determine the column number for Singapore, so that when the lookup criteria change, the column number updates automatically.

=MATCH(B9,A1:D1,0)

The result is 2, which is correct since Singapore is the second column in the table (Table 5).

Table 5

Now, return to the VLOOKUP formula and replace argument 3 with the MATCH function reference (highlighted in Table 6).

Table 6

With this setup, changing a different country in B9 automatically updates the VLOOKUP result, eliminating the need to adjust column numbers manually.

NESTING FORMULAS FOR A CLEANER WORKSHEET

Now, let’s streamline the formula by using only one cell; this technique is called “nesting”.

Nesting one function within another is actually very simple. Experts can quickly nest the MATCH function within VLOOKUP but, if you’re not familiar with nesting, follow this approach.

Since the VLOOKUP formula references B11 (highlighted in Table 6), simply copy the formula from B11 (excluding the equal sign) and replace the reference to B11 with it (highlighted in Table 7). This allows us to remove B11 entirely.

Table 7

Nesting makes a worksheet look cleaner. Nesting two functions is usually fine but avoid excessive nesting as it can make formulas overly complex, reduce readability, and make future amendments more challenging.

WHY XLOOKUP ISN’T PREFERRED IN THIS CONTEXT

XLOOKUP is a powerful function, but in this case, it doesn’t offer the same level of efficiency as VLOOKUP. The key difference between the two is how the column to be returned is specified:

• VLOOKUP requires a column index number
• XLOOKUP requires a column reference

To dynamically determine a relative column index, we can use MATCH, which is efficient. However, to dynamically determine a column reference for XLOOKUP, we need OFFSET, (a volatile function) or INDEX + MATCH (another nested function).

Excessive use of volatile functions in Excel can cause performance issues, making recalculations slower. Using another nested function increases the number of calculations Excel must perform. Therefore, VLOOKUP + MATCH is a better choice for handling two-dimensional lookups efficiently.