Retrieving a Value from List that meets multiple Criteria

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.

Hi!

Try this:

E1 = 1002
F1 = Cost 2

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

Biff

"mamalik" <[email protected]> wrote in
message news:[email protected]...
>
> 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 |
> +-------------------------------------------------------------------+
>
> --
> mamalik
> ------------------------------------------------------------------------
> mamalik's Profile:
> http://www.excelforum.com/member.php...o&userid=30049
> View this thread: http://www.excelforum.com/showthread...hreadid=497298
>

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.

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.

mamalik wrote:
>
> 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.
>
> --
> mamalik
> ------------------------------------------------------------------------
> mamalik's Profile: http://www.excelforum.com/member.php...o&userid=30049
> View this thread: http://www.excelforum.com/showthread...hreadid=497298

--

Dave Peterson