2way lookup

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   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?

2 way lookup excel

 

=SUMPRODUCT((Order_dates=H4)*(Customers=H3)*Ordered_Quantity) =267

NB: Below are the references of the named ranges

Order_dates = A3:A19

Customers = C2:F2

Ordered_Quantity =C3:F19

How this Works;

(Order_dates=H4) creates a Boolean array showing TRUE for the Date criteria.

{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE}

(Customers=H3) creates a Boolean array showing TRUE for the Customer criteria.

{FALSE,TRUE,FALSE,FALSE}

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

2. USING ARRAY FUNCTION–SUM

{=SUM((Order_dates=H7)*(Customers=H6)*Ordered_Quantity)} =267

The arrary functions follows the same  truth table logic. i.e. Forms a matrix where the criteria are met and multiplies it with ordered quatity matrix and sums the totals

2way lookup matrix

 

3. SUMIFS, INDEX & MATCH

If you do not understand the logic in conjuction truth table  and you do not like array fomulas then you can use SUMIFS, INDEX & MATCH

=SUMIFS(INDEX(Ordered_Quantity,,MATCH(H9,Customers,0)),Order_dates,H10)

2 WAY LOOKUP

How it works;

INDEX(Ordered_Quantity,,MATCH(H9,Customers,0)) Selects the Column to sum dynamically. This provides SUMIF with a sum range

{55;23;25;27;29;31;33;35;37;39;41;43;45;47;49;51;21}

►SUMIFS takes in this Sum Range, filters it further using the Date criteria and then sums the array

4. DSUM

=DSUM(Data_Base,H12,H13:H14)=267

►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

2WAYLOOK

=SUM(selected_orders)

The trick here is to first create a named range that will select the orders. Therefore;

selected_orders= (Order_dates=$H$17)*(Customers=$H$16)*Ordered_Quantity

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 (

(Order_dates=$H$17)*(Customers=$H$16)*Ordered_Quantity) Named Ranges

DOWNLOAD ABOVE EXAMPLE SPREADSHEET

RELATED ARTICLES

LOOKUP AND SUM IN EXCEL

SUMPRODUCT WITH WILD CARDS

 

2 thoughts on “2 WAY LOOKUP IN EXCEL”

  1. Kevin says:

    DSUM is the way to go. Most people ignore the all the “D” formulas which is a shame.

    1. Thanks Kelvin. I think Database Functions are not that popular this is why they are mostly ignored.

Leave a Comment:

Your email address will not be published. Required fields are marked *