Excel Lookup Problem

C

Crosswire

Example Data;

AAB01D P511 4
AAB01D P512 1
AAB01D P513 5
AAB01D P514 1
AAB01D P515 2
AAB01E P511 6
AAB01E P512 2
AAB01E P513 6
AAB01E P514 2
AAB01E P515 2

I need a formula to in the above example lookup AAB01D, then looku
P513 and return the value in the 3rd column. Eg, Answer would be 5.
The lookup values (AAB01D and P513) must be interchangeable - i.e. If
change P513 in the formula to P514 then the result would be one.

I am stumped. To anyone that can help - I would appreciate it
 
F

Frank Kabel

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100="AAB01D")*(B1:B100="P513"),0))


If column C contains only numbers you may also use
=SUMPRODUCT((A1:A100="AAB01D")*(B1:B100="P513"),C1:C100)
 
D

David Prout

I had a similar problem but with more columns. Way round I used was to add a column with the values
concatenated. i.e. assuming your existing data starts in cells A1,B1 and C1. Insert a column C and
add the formula +A1&B1. copy this down as far as you need.
Put your criteria tp look up where you wish e.g cells F10 and G10 and then wherever you want the
result e.g. I10 put the formula =VLOOKUP(F10&G10,C1:D10,2). You can then hide column C if you
wish.

Not sure if there are more elegant ways of doing this but this works for multiple criteria very well
and is fairly flexible.

DavidP
 
Top