Validation, VlookUp

C

Comicfly

I have a table with 4 columns. 3 columns (Product, Mode,
City) have data that I choose in 3 validation lists. In
turn, I want to match up (link) the Product, Mode and
City from the lists, look them up in the table to get the
number of units. I tried to use the offset /vlookup
options but could not match the 3 items. As an example:
Product: A, Mode: TC, City: Salem. In matching these the
Number of units is 34. Formula would go in the yellow
cell. Attached is the spreadsheet. Thanks......Comicfly
Product Mode location Number of

A TT NYC 20 A
A TC Salem 34 TC
A DR Richmond 6 Salem
A Tote Houston 45
B TC Miami 23
B DR Chicago 12
C TT LA 46
C TC San Diego 4
C DR Tulsa 16
 
K

Ken Wright

=SUMPRODUCT((RangeA=Cellref1)*(RangeB=Cellref2)*(RangeC=Cellref3)*(RangeD))

Assuming you have 3 cells cellref1,2,3 that contain the criteria for your 3
columns Prod/Mode/City, then the formula above when substitued with real ranges
will sum all units found in RangeD that match criteria for A/B/C
 
Top