What is 2-way Lookup?
Credit www.pxfuel.com

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 (Item Details)

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.

Table 2 (Cost of units)


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).

Table 2

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.

Table 1 Modified

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.


要查看或添加评论,请登录

Adit Goswami的更多文章

  • HR decision making using several leaves analysis.

    HR decision making using several leaves analysis.

    Leaves are essential for an employee to maintain their mental well-being, maintaining operational efficiency and…

  • Getting started with Power BI

    Getting started with Power BI

    This is my first project on data analysis using Power BI. I started using MS Excel a month ago for data analysis…

    4 条评论

社区洞察

其他会员也浏览了