CODEWITHSUNDEEP
excelexcel-formulamultiple-columnsvlookuplookup
Zach
Zach

Reputation: 1351

Lookup data in Excel matching multiple columns

http://imgur.com/BYejKRb

As you can see in the picture above, I want to take Table 1 and, using formulas that can auto-populate, turn it into Table 2.

I tried using VLOOKUP and INDEX, but I'm not 100% sure on how to use the latter and the former allows you to only use one lookup value - I need it to be able to match up both the date and the product.

This needs to be formula-based because table 1 automatically updates based on a data feed.

Upvotes: 0

Views: 537

Answers (2)

ZygD
ZygD

Reputation: 24386

The following formula will do it (array formula which has to be entered using Ctrl + Shift + Enter):

=INDEX($C$5:$C$9,MATCH(1,($B$5:$B$9=$E5)*($A$5:$A$9=F$4),0))

I assume that:

  • your data of Table 1 is in A5:C10
  • E5 has the value of "Widget"
  • F4 has the date value

This array formula should be entered in F5.

Upvotes: 1

Steven Martin
Steven Martin

Reputation: 3272

If the picture is what your trying to do , then dont bother with the lookup in two columns, just use a PIVOT TABLE and you will have the data in seconds , you can refresh the pivot with one click or even automatically if you need it

Upvotes: 0

Related Questions