Retrieving a Value from List that meets multiple Criteria

[mamalik]

Hi,

I need to retrieve a value (or item) from a list that meets multiple
criteria in another list. I could not figure this out. Consider the
following table (also attached as Table1.gif).

Table1 (Base Table from where values are to be extracted)

Account Description CostCentre Amount
1001 Account 1 Cost 1 1,000
1001 Account 1 Cost 2 1.500
1002 Account 2 Cost 1 2,500
1002 Account 2 Cost 2 3,000
1003 Account 3 Cost 1 4,200
1003 Account 3 Cost 2 3,500

In another table which contains the account and cost centre columns, I
want to get the amount for example for Account 1002 / Cost 2 (i.e.
3,000). Is there any formula which looks up the base table and
automatically extracts the required value? Please note that in this
example two criteria are required to be matched i.e. Account and
CostCentre. I tried to use ‘vlookup’ formula with ‘and’ function but
could not get the results.


+-------------------------------------------------------------------+
|Filename: Table1.gif |
|Download: http://www.excelforum.com/attachment.php?postid=4173 |
+-------------------------------------------------------------------+

 

[Biff]

Hi!

Try this:

E1 = 1002
F1 = Cost 2

=SUMPRODUCT(--(A2:A7=E1),--(C2:C7=F1),D27)

Biff

 

[mamalik]

Hi Biff

Thanks a lot. The formula worked. However, there is one little problem.
The formula only retrieves numerical value. If we substitute the amount
column by an alpha value, this results as '0'. Could you please advise
on that.

 

[Dave Peterson]

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet!$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.