vlookup question

[knox5731]

I'm trying to write vlookup that will lookup cells in a different
worksheet. However, there are two matches on the other worksheet. How
can I get it to sum up those two matches and then put it as my value.

I'm looking for something like this but I don't know how to get it to
work with multiple matches in the vlookup

=SUM(Vlookup($A$74&"FINV"&$A$75,'Est-Inv Data'!$F:$S,D$70,FALSE))


Help?

Thanks!
knox

 

[Jim Thomlinson]

You probably want a sumproduct formula... Here is a link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[L. Howard Kittle]

If I understand correctly...

=vlookup(1st_match,your_range,your_column,0)+vlookup(2nd_match,your_range,your_column,0)

HTH
Regards,
Howard

 

[knox5731]

I tried the adding the vlookups, but the vlookup won't go past the
first match. The sumproduct doesn't work or sumif that I tried -- I
don't think it knows how to do the lookup and then sum multiple
matches....


I think I need the vlookup in a loop or something but I"m not sure how
to make it go on to the next match and sum it up.

=sumif('Est-Inv Data'!$F:$S,$A$74&"FINV"&$A$75,d$70)

 

[L. Howard Kittle]

So the 1st match and the 2nd match are identical? If so then vlookup is of
no use. I don't know enough of the magic behind sumproduct to be of help,
sorry.

Regards,
Howard

 

[knox5731]

Yep....they match, but their subsequent values in other columns need to
be summed....Here is an example

Worksheet one
column A
Row 74 A023
Row 75 2006

Vlookup($A$74&"FINV"&$A$75,'Est-Inv Data'!$F:$S,D$70,FALSE)
So, I'm looking for A023FINV2006 on the EST-INV DAta sheet...
Two rows match that, and I want to pull their data and have it summed
together......

 

[L. Howard Kittle]

Hmmm,,,

Can you send me a example worksheet to look at?

Howard

 

[knox5731]

Worksheet one
column A
Row 74 A023
Row 75 2006

Worksheet two (EST-INV DATA -- range $F:$S)

Column F G H I
A023FINV2006 10 20 20
A023FINV2006 2 5 1
A2045FINV2006 1 3 2


So...I'm try look on worksheet one and concatenate certain data, which
is what i will use to search on worksheet two....then on worksheet two
since there are two matches in column F for my data, I want it to sum
up both of the numbers in column G, and put that as the result of my
formula

a single vlookup will stop at the first match it reaches and then
stop....so all I get is the value 10 so far...
does that help?

 

[L. Howard Kittle]

Vookup will not work if there are multiple lookup values in the lookup
range.
Sumproduct is probably the way to go. Will study it a bit.

Howard

 

[L. Howard Kittle]

This worked for me on your data sample, returned 12.

=SUMIF(E1:E3,B1,F1:F3)

Where B1 holds A023FINV2006, which may be the result of a formula to
concatenate your two other values.

HTH
Regards,
Howard

 

[knox5731]

That worked with the sumif stuff!! Thanks a bunch for the help!

 

[L. Howard Kittle]

Glad to help. Actually I went to the link Jim offered and found an example
that matched your case and used it.

Regards,
Howard

 

[Jim Thomlinson]

Bookmark that web site. Now that you know about sumproduct you will probably
use it a fair bit. I know that I do... It is more versatile that sumif in
that is multiplies, sums and counts. Sumif is good in simple cases though and
if it works then so much the better.