Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup on multiple items
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup on multiple items
=SUMPRODUCT(--(a2:a3="0020"),--(b2:b3="RIC"),c2:c3)
"Bazy2k" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup on multiple items
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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup on multiple items
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. "EricBB" wrote: =SUMPRODUCT(--(a2:a3="0020"),--(b2:b3="RIC"),c2:c3) "Bazy2k" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup on multiple items
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 he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Bazy2k wrote: 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. "EricBB" wrote: =SUMPRODUCT(--(a2:a3="0020"),--(b2:b3="RIC"),c2:c3) "Bazy2k" wrote: 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. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use VLookup for Multi items? | Excel Discussion (Misc queries) | |||
Vlookup, HLOOKUP, To find Multiple items and then sum all in colum | Excel Discussion (Misc queries) | |||
Want Vlookup to list multiple items with the same key? | Excel Worksheet Functions | |||
order form with multiple items and sizes for items | Excel Discussion (Misc queries) | |||
Vlookup for 2 items | Excel Worksheet Functions |