Vlookup on multiple items

[Bazy2k]

Hi guys

I have a list of data, 3 columns. Eg.

Code CostCentre Amount
0020 RIC 200.00
0020 LPM 350.00

As you can see the two items have the same code (0020) but different
CostCentre codes (RIC, LPM).
I want to be able to use vlookup (or any other means) to look for data which
has a code or 0020 AND a costcentre of RIC (or LPM). Problem obv with vlookup
is it only looks at the left-most side of data. How can i add an AND
statement to it so it can look up both pieces of data and gives me a result?

Thanks for your time.
Tom.

 

[EricBB]

=SUMPRODUCT(--(a2:a3="0020"),--(b2:b3="RIC"),c2:c3)

 

[Jonathan Cooper]

Another solution would be to add a helper column in front of your 'code'
column. Then concatenate the code and cost center together (e.g., 0020RIC)
using a formula. copy/paste the formula in front of all your data. Then do
the vlookup on the concatenation.

 

[Bazy2k]

Thanks Eric but i cant seem to get that to work? What do i have to replace
the '--' with?

What im trying to do is pick the amounts out from the list of codes and cost
centres i have, so i have the list of codes and cost centres here but with no
values yet, i need to be able to look into the download and pick out the
relevant amount relating to that specific code AND cost centre?

so something like =vlookup("0020" and "RIC", Array, 3)?!

Thanks
Tom.

 

[Dave Peterson]

Try the formula as written--don't change anything.

You can djust the ranges to match what you need--but you can't use whole columns
(except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html