How to Unlock MS Excel lookup power to the next level? Part 1

When we were asked about our Excel score during the interview for our first job, I believe most of us gave 7–9 points out of 10 normally. This number actually went down in our mind when we start to use Excel in the work especially when we learn what vlookup/ xlooup/ pivot table are.

Knowing these lookup functions are great! But, what’s next?

In this article, we are going to show you how to leverage few more useful functions to bring your vlookup, xlookup skill to the next level.

Raw Data Structure

Tab A: Demand at the product type/ product/ UOM level: Subset of the source data.

Tab A sample data

Tab B: Demand aggregated into product level

Tab B sample data

Tab C: Delivery cost per unit at the product level by UOM

Tab C sample data

Q1: How to get the demand and delivery expense information to calculate the total delivery expense for each combination of product and UOM for the following given table?

Q1

Q1 Solutions

Method 1 (vlookup)

• Qty: Need to concatenate "Products" & "UOM" in Tab A.

VLOOKUP(B4&C4, 'Tab A'!$D$4:$E$68,2,0)

Tab A: Concatenate “Products” & “UOM”

• Rate: Leverage IF statement to look up information depends on UOM values.

=VLOOKUP(B4,IF(C4="Single",'Tab C'!A$3:B$55,'Tab C'!E$3:F$55),2,FALSE)

Method 2 (xlookup)

• Qty: No need to concatenate values in Tab A, just use & or * to achieve multi-columns join.

=XLOOKUP(B4&C4,'Tab A'!B$4:B$68&'Tab A'!C$4:C$68,'Tab A'!E$4:E$68)

=XLOOKUP(1,('Tab A'!B$4:B$68=Q1c!B4)*('Tab A'!C$4:C$68=Q1c!C4),'Tab A'!E$4:E$68)

• Rate: Leverage IF statement to look up information depends on UOM values.

=IF(C4="Single",XLOOKUP(B4,'Tab C'!A$3:A$55,'Tab C'!B$3:B$55),XLOOKUP(B4,'Tab C'!E$3:E$55,'Tab C'!F$3:F$55))

Bonus (Index and Match)

The INDEX function can return a specific value in a one-dimensional range and the the MATCH function can return the position of a value in a given range. We can leverage the property of these two functions to do what vlookup/ xlookup can do.

• Qty:.

=INDEX('Tab A'!E$4:E$68,MATCH(Q1a!B4&Q1a!C4,'Tab A'!B$4:B$68&'Tab A'!C$4:C$68,0))

• Rate:

=IF(C4="Single",INDEX('Tab C'!$B$3:$B$55,MATCH(B4,'Tab C'!$A$3:$A$55,0)),INDEX('Tab C'!$F$3:$F$55,MATCH(B4,'Tab C'!$E$3:$E$55,0)))

Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)

𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲