M
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 |
+-------------------------------------------------------------------+
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 |
+-------------------------------------------------------------------+