software

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)

AccountDescriptionCostCentreAmount
1001Account 1Cost 11,000
1001Account 1Cost 21.500
1002Account 2Cost 12,500
1002Account 2Cost 23,000
1003Account 3Cost 14,200
1003Account 3Cost 23,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: www.excelforum.com/attachment.php?postid=4173 |
-------------------------------------------------------------------

--
mamalik
------------------------------------------------------------------------
mamalik's Profile: www.excelforum.com/member.php...oamp;userid=30049
View this thread: www.excelforum.com/showthread...hreadid=497298Hi!

Try this:

E1 = 1002
F1 = Cost 2

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

Biff

quot;mamalikquot; gt; wrote in
message ...
gt;
gt; Hi,
gt;
gt; I need to retrieve a value (or item) from a list that meets multiple
gt; criteria in another list. I could not figure this out. Consider the
gt; following table (also attached as Table1.gif).
gt;
gt; Table1 (Base Table from where values are to be extracted)
gt;
gt; Account Description CostCentre Amount
gt; 1001 Account 1 Cost 1 1,000
gt; 1001 Account 1 Cost 2 1.500
gt; 1002 Account 2 Cost 1 2,500
gt; 1002 Account 2 Cost 2 3,000
gt; 1003 Account 3 Cost 1 4,200
gt; 1003 Account 3 Cost 2 3,500
gt;
gt; In another table which contains the account and cost centre columns, I
gt; want to get the amount for example for Account 1002 / Cost 2 (i.e.
gt; 3,000). Is there any formula which looks up the base table and
gt; automatically extracts the required value? Please note that in this
gt; example two criteria are required to be matched i.e. Account and
gt; CostCentre. I tried to use 'vlookup' formula with 'and' function but
gt; could not get the results.
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Table1.gif |
gt; |Download: www.excelforum.com/attachment.php?postid=4173 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; mamalik
gt; ------------------------------------------------------------------------
gt; mamalik's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30049
gt; View this thread: www.excelforum.com/showthread...hreadid=497298
gt;

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: www.excelforum.com/member.php...oamp;userid=30049
View this thread: www.excelforum.com/showthread...hreadid=497298You 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:
gt;
gt; Hi Biff
gt;
gt; Thanks a lot. The formula worked. However, there is one little problem.
gt; The formula only retrieves numerical value. If we substitute the amount
gt; column by an alpha value, this results as '0'. Could you please advise
gt; on that.
gt;
gt; --
gt; mamalik
gt; ------------------------------------------------------------------------
gt; mamalik's Profile: www.excelforum.com/member.php...oamp;userid=30049
gt; View this thread: www.excelforum.com/showthread...hreadid=497298

--

Dave Peterson