Vlookup

**headley4ever** · 12-06-2010, 11:17 AM

Hi everyone,

Can anyone help me with this,

I need to write a vlookup or any formula that will look for three (3) item numbers in coulmn 'A' and sum there total in column 'C' & 'D'

So in column 'A' i have the item numbers, in coulmn 'B' i have the name of the Item and in columns 'C' & 'D' i have the amount that is sold for each item.

Some of these items has more than one item# therefore i want to be able to type in ALL the Item # for these items that has more than one item number and then sum the amount that is sold for these items.

I tried this formula but it dosent work SUM (VLOOKUP(101,205,315,A1:D50,{3,4},0))

**NBVC** · 12-06-2010, 11:23 AM

Try:

=SUMPRODUCT(ISNUMBER(MATCH(A1:A50,{101,205,315},0))*(C1:D50))

This will add the values in column C and D together for each match in column A.... is that what you want?

**headley4ever** · 12-06-2010, 12:10 PM

Thanks for your quick response. You have correctly interpeted my question. The formula however is not working. I am getting this #VALUE! error message.

Is their something else i should do to make this formula work? Should i use CTRL+ENTER

Thanks Again

**NBVC** · 12-06-2010, 12:35 PM

No need for special entry.

Are you values in column A actually formatted as numbers or text?

Try selecting column A and go to Data|Text to Columns and click Finish

Or do you have text in columns C or D?

Or are there Value errors in A, C or D

See attached for working sample

**headley4ever** · 12-06-2010, 12:59 PM

Many Thanks ....... The formula now works. I had a few text in column 'A' which i have now deleted. Am i correct in saying that this column will not work if Text is in the column. Is their a way we could write the formula to ignore text?

**NBVC** · 12-06-2010, 01:09 PM

Text in column A should not be the problem. It's text in columns C and D

Try:

=SUMPRODUCT(ISNUMBER(MATCH(A1:A50,{101,205,315},0))*ISNUMBER(C1:D50),C1:D50)