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

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

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

Jim Thomlinson


" wrote:

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

If I understand correctly...

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

HTH
Regards,
Howard

wrote in message
oups.com...
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

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)

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

wrote in message
oups.com...
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

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......

Hmmm,,,

Can you send me a example worksheet to look at?

Howard

"L. Howard Kittle" wrote in message
...
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

wrote in message
oups.com...
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

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?

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

wrote in message
oups.com...
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?

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

"L. Howard Kittle" wrote in message
. ..
Hmmm,,,

Can you send me a example worksheet to look at?

Howard

"L. Howard Kittle" wrote in message
...
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

wrote in message
oups.com...
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

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

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

Regards,
Howard

wrote in message
oups.com...
That worked with the sumif stuff!! Thanks a bunch for the help!

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.
--
HTH...

Jim Thomlinson


"L. Howard Kittle" wrote:

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

Regards,
Howard

wrote in message
oups.com...
That worked with the sumif stuff!! Thanks a bunch for the help!