2 WAY LOOKUP IN EXCEL
The ability to lookup values is a MUST have skills for all Excel users. If you cannot lookup you cannot excel in data analysis.
In this article, I will show you 5 ways you can do a 2-way lookup in Excel.
1. USING SUMPRODUCT
For example, using below sales data lookup and sum the total quantity for ALL items ordered by Brosina Hoffman on 28-08-2010?
NB: Below are the references to the named ranges
Order_dates = A3:A19
Customers = C2:F2
How this Works;
►(Order_dates=H4) creates a Boolean array showing TRUE for the Date criteria.
►(Customers=H3) creates a Boolean array showing TRUE for the Customer criteria.
►SUMPRODUCT converts the two Boolean arrays into its numeric equivalent of 1 & o and then using the techniques of conjunction truth table, adds up the data in the intersections 55+27+29+41+43+51+21 = 267
What if you need to sum total orders for more than one customer?
What is the total quantity of ALL items ordered by Brosina Hoffman & Irene Maddox on 28-08-2010?
The trick is including the 2 customers in the function
(Customers=H4)+(Customers=H3)–this ensures the two columns are dynamically selected.
2. USING ARRAY FUNCTION–SUM
The array functions follow the same truth table logic. i.e. Forms a matrix where the criteria are met and multiplies it with ordered quantity matrix and sums the totals
3. SUMIFS, INDEX & MATCH
If you do not understand the logic in conjunction truth table and you do not like array formulas then you can use SUMIFS, INDEX & MATCH
How it works;
►INDEX(Ordered_Quantity,,MATCH(H9,Customers,0)) Selects the Column to sum dynamically. This provides SUMIF with a sum range
►SUMIFS takes in this Sum Range, filters it further using the Date criteria and then sums the array
►Data_Base refers to the whole table from A2:F19–Includes the headings
How this works:
DSUM function sums the numbers in a column given the database it contains, column Position/Column Heading and a Criteria
DSUM( Data_base, Column_Heading, criteria )
5. DEFINED NAMED RANGES
The trick here is to first create a named range that will select the orders. Therefore;
Then SUM the Named range
How to Create the Selected_Order Name Range
Go to Formulas►Define Name►Type the name you want►In the refers to section Paste your range (