Excel 2010 Magic Trick 821: 4-Way Lookup When There Are No Duplicates 2007 and 2003 Methods

See how to do a four way lookup when there are no duplicates
See 4 methods:
1. SUMIFS function
2. Helper Column Method using AND and VLOOKUP functions
3. LOOKUP function and Boolean arrays
4. INDEX and MATCH with mashed up columns using the ampersand Join Symbol &

[excelisfun]

@udanial, that is a good one too for pre-2007!!

[excelisfun]

@CraigHatmaker, sure - you could use the mash up with LOOKUP! If you are using Excel 2010, SUMIFS is probably faster in caculation. I did not time them, though...

[planiolro]

Indeed an awesome video; the lookup and index method are really cool when u do not have 2007/2010. But what happens when u have duplicates?Once again a really awesome video!

[excelisfun]

@pssmrteo, SUMIFS is a function programmed by Microsoft to be very fast in calculating. I did not time these, but I would guess that SUMIFS is fastest, VLOOKUP and Helper would be 2nd, then INDEX MATCH, then LOOKUP.

[excelisfun]

@planiolro, it depends on whether you want to add the values or display multiple items.

SUMIFS will work for adding.

For the Helper Column method amend the helper column to:

=SUM(AND(B10=$A$2, C10=$B$2, D10=$C$2, E10=$D$2), A9)

and lookup would be (placed in cell I9):

=IFERROR(VLOOKUP(COLUMNS($I9:I9), $A$10:$F$29, 6, 0), "") (2007)

=IF(ISERROR(VLOOKUP(COLUMNS($I9:I9), $A$10:$F$29, 6, 0)), "", VLOOKUP(COLUMNS($I9:I9), $A$10:$F$29, 6, 0)) (2003)

then copy to left.

[pssmrteo]

Great tips. Would any of these 4 methods be faster than the rest?

[CraigHatmakerBXL]

Brilliant. But why not use the mash technique with LOOKUP?

I'm using XL2010's Structured Table References here for an example and named the table "Data" which makes the whole thing act like a dynamic range, but the idea works the same using the $B$10:$B$29 references as well. I also named the input cells in row 2 "Date", "SR", "Product", "Customer" for clarity.

=LOOKUP(Date&SR&Product&Customer, Data[Date]&Data[SR]&Data[Product], Data[Customer])