What is 2-way Lookup?

Recently, I explored some advanced Excel concepts and found the 2-way lookup particularly fascinating. This concept is both intriguing and thought-provoking. In this short article, you can learn how it works and see a practical situation where it can be helpful. Let's take an example that we have to calculate total cost of transportation for below items.

Table 1 contains Item and Quantity data. To add the "Cost/unit" and "Total Cost" columns to this table 1, you can follow these steps:

1. Cost/unit: Determine the cost per unit for each item.

2. Total Cost: Multiply the quantity of each item by its cost per unit.

Now let's take the reference of second table Table 2.

If you carefully examine Table 2, it resembles a 2D array. For instance, transporting Item "Planet7" with units between 5 and 20 will cost you 0.85 per unit. We can implement a two-way lookup to find the cost per unit using the formula:

=XLOOKUP (lookup value, lookup array, return array)

lookup value will be item from table 1 such as "Raddish", "Planet7" etc.

lookup array will be list of items or array from table 2.

The tricky part is returning an array where we have to implement another XLOOKUP. So, it can be written as XLOOKUP (quantity from table 1, [1,5,20,50] from table 2, 2D array representing the cost/unit, -1 for choosing exact match or next smaller item).

The GREEN color signifies the lookup array for quantity and VIOLET color represents the 2D array for cost/unit. The final outcome will be like this below.

For the item "Planet7" with a quantity of 20, the cost per unit will be 0.8. This is based on the 2D array in Table 2, which indicates that transporting 20 units of "Planet7" costs 0.8 per unit.