Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


--
James.

Hi,
could you be a little more specific, where is the 22 coming from?, do you
have another list in the same worksheet or in a different worksheet

"James" wrote:

Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


--
James.

On Wed, 22 Jul 2009 05:10:01 -0700, James
wrote:

Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


Try this formula:

=SUMPRODUCT(--((A1:A100="ZZ")+(B1:B100="ZZ")+(C1:C100="ZZ")0)*( D1:D100))

Change 100 in all places to fit the number od rows with data you have.
This formula will return the sum in column D for all rows where there
is a "ZZ" in either of the columns A to C.

Hope this helps/ Lars-Åke

Hi Eduardo

The 22 is coming from another list on a separate worksheet in the same
document. so I have a list elsewhere where it shows a list of values with
results one of which is value "ZZ" with the result of "22". So I am trying to
find "ZZ" in 3 columns and return the result.


--
James.


"Eduardo" wrote:

Hi,
could you be a little more specific, where is the 22 coming from?, do you
have another list in the same worksheet or in a different worksheet

"James" wrote:

Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


--
James.

You can use this UDF. Open VBE (Alt+F11), Insert - Module. Paste this in:

'================
Function MultiLookup(Lookup_Value, _
Lookup_Array As Range, Return_Array As Range)

With Lookup_Array
Set c = .Find(Lookup_Value, LookIn:=xlValues)
MultiLookup = Cells(c.Row, Return_Array.Column).Value
End With

End Function
'================

Back in your workbook, your formula would be:
=MultiLookup("ZZ",A:C,D:D)


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"James" wrote:

Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


--
James.

Hi,
Let's assume that your list is in sheet2 in column A and the numbers you
want to pull in column B, starting in row 2, I assume that the letter you
want to find will be in cell D1 then in column D enter

=IF(OR(D1=A2,D1=B2,D1=C2),VLOOKUP(D1,Sheet2!$A$1:$ B$10,2,FALSE))

if you are using excel 2007

=IF(OR(D1=A2,D1=B2,D1=C2),VLOOKUP(D1,Sheet2!A:B,2, FALSE))

change ranges to fit your needs


"James" wrote:

Hi Eduardo

The 22 is coming from another list on a separate worksheet in the same
document. so I have a list elsewhere where it shows a list of values with
results one of which is value "ZZ" with the result of "22". So I am trying to
find "ZZ" in 3 columns and return the result.


--
James.


"Eduardo" wrote:

Hi,
could you be a little more specific, where is the 22 coming from?, do you
have another list in the same worksheet or in a different worksheet

"James" wrote:

Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


--
James.

Thanks Lars-Ã…ke

That's great it works! Another question though, what happens if "ZZ" appears
more than once i.e. more than one row has a "ZZ", it'll add them together
e.g. 2 "ZZs" would give a result of 44. How would you get around that?!


--
James.


"Lars-Ã…ke Aspelin" wrote:

On Wed, 22 Jul 2009 05:10:01 -0700, James
wrote:

Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


Try this formula:

=SUMPRODUCT(--((A1:A100="ZZ")+(B1:B100="ZZ")+(C1:C100="ZZ")0)*( D1:D100))

Change 100 in all places to fit the number od rows with data you have.
This formula will return the sum in column D for all rows where there
is a "ZZ" in either of the columns A to C.

Hope this helps/ Lars-Ã…ke

What do you want to happen? What I mean is, should formula return first
result, or last result?


Example:
ZZ ZA ZX 22
AB CA ZZ 25

Should formula return 22, or 25, or 47?
(another possible error is if ZZ appears in both column A & C, would also
create a double adding)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"James" wrote:

Thanks Lars-Ã…ke

That's great it works! Another question though, what happens if "ZZ" appears
more than once i.e. more than one row has a "ZZ", it'll add them together
e.g. 2 "ZZs" would give a result of 44. How would you get around that?!


--
James.


"Lars-Ã…ke Aspelin" wrote:

On Wed, 22 Jul 2009 05:10:01 -0700, James
wrote:

Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


Try this formula:

=SUMPRODUCT(--((A1:A100="ZZ")+(B1:B100="ZZ")+(C1:C100="ZZ")0)*( D1:D100))

Change 100 in all places to fit the number od rows with data you have.
This formula will return the sum in column D for all rows where there
is a "ZZ" in either of the columns A to C.

Hope this helps/ Lars-Ã…ke

Well, what result do you expect if there is "ZZ" on more than one
row? If the corresponding value in columns D always be the same, e.g.
22, I guess you want that to be the result, but if there are different
values in columns D for the "ZZ" rows, then what should the result be?
The average, the sum, or what?

Lars-Åke

On Wed, 22 Jul 2009 05:45:01 -0700, James
wrote:

Thanks Lars-Åke

That's great it works! Another question though, what happens if "ZZ" appears
more than once i.e. more than one row has a "ZZ", it'll add them together
e.g. 2 "ZZs" would give a result of 44. How would you get around that?!

On Wed, 22 Jul 2009 05:51:01 -0700, Luke M
wrote:

What do you want to happen? What I mean is, should formula return first
result, or last result?


Example:
ZZ ZA ZX 22
AB CA ZZ 25

Should formula return 22, or 25, or 47?
(another possible error is if ZZ appears in both column A & C, would also
create a double adding)

I think the formula I proposed will take care of the case when ZZ
appears in more than one column on the same row.
It will not be any "double adding" in that case.

Lars-Åke

Hi Luke

Doesn't matter which result it returns as it'll always be the same e.g. in
your example both rows would just show 22 rather than 22 and 25 as you have
done. ZZ will never appear in two columns on the same row only once.

P.S. Thanks for your advice on the UDF, bit beyond my knowledge but I'll
give it a go over the next few days ;)


--
James.


"Luke M" wrote:

What do you want to happen? What I mean is, should formula return first
result, or last result?


Example:
ZZ ZA ZX 22
AB CA ZZ 25

Should formula return 22, or 25, or 47?
(another possible error is if ZZ appears in both column A & C, would also
create a double adding)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"James" wrote:

Thanks Lars-Ã…ke

That's great it works! Another question though, what happens if "ZZ" appears
more than once i.e. more than one row has a "ZZ", it'll add them together
e.g. 2 "ZZs" would give a result of 44. How would you get around that?!


--
James.


"Lars-Ã…ke Aspelin" wrote:

On Wed, 22 Jul 2009 05:10:01 -0700, James
wrote:

Hello there

I am trying to do a vlookup off multiple columns. For example below if I am
trying to find the result of "22" for a value "ZZ" in columns A to C below
how to I tell it to lookup in all 3 columns i.e. A-C and return the result in
column D?


A B C D
ZX ZZ ZA 22


Try this formula:

=SUMPRODUCT(--((A1:A100="ZZ")+(B1:B100="ZZ")+(C1:C100="ZZ")0)*( D1:D100))

Change 100 in all places to fit the number od rows with data you have.
This formula will return the sum in column D for all rows where there
is a "ZZ" in either of the columns A to C.

Hope this helps/ Lars-Ã…ke

Hi Lars-Ã…ke hopefully response to Luke answered this, i.e.

Doesn't matter which result it returns as it'll always be the same e.g. in
your example both rows would just show 22 rather than 22 and 25 as you have
done. ZZ will never appear in two columns on the same row only once.
--
James.


"Lars-Ã…ke Aspelin" wrote:

Well, what result do you expect if there is "ZZ" on more than one
row? If the corresponding value in columns D always be the same, e.g.
22, I guess you want that to be the result, but if there are different
values in columns D for the "ZZ" rows, then what should the result be?
The average, the sum, or what?

Lars-Ã…ke

On Wed, 22 Jul 2009 05:45:01 -0700, James
wrote:

Thanks Lars-Ã…ke

That's great it works! Another question though, what happens if "ZZ" appears
more than once i.e. more than one row has a "ZZ", it'll add them together
e.g. 2 "ZZs" would give a result of 44. How would you get around that?!

On Wed, 22 Jul 2009 06:00:01 -0700, James
wrote:

Hi Luke

Doesn't matter which result it returns as it'll always be the same e.g. in
your example both rows would just show 22 rather than 22 and 25 as you have
done. ZZ will never appear in two columns on the same row only once.

P.S. Thanks for your advice on the UDF, bit beyond my knowledge but I'll
give it a go over the next few days ;)


This formula will take care of the possibility that "ZZ" is found on
more than one row.

=SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")0)*(D1: D10))/SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")0))

Hope this helps / Lars-Åke

On Wed, 22 Jul 2009 13:07:11 GMT, Lars-Åke Aspelin
wrote:

On Wed, 22 Jul 2009 06:00:01 -0700, James
wrote:

Hi Luke

Doesn't matter which result it returns as it'll always be the same e.g. in
your example both rows would just show 22 rather than 22 and 25 as you have
done. ZZ will never appear in two columns on the same row only once.

P.S. Thanks for your advice on the UDF, bit beyond my knowledge but I'll
give it a go over the next few days ;)


This formula will take care of the possibility that "ZZ" is found on
more than one row.

=SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")0)*(D1: D10))/SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")0))

Hope this helps / Lars-Åke


And if you know that ZZ is never found on more than one column on any
row, the formula can be shortened a bit, like this:

=SUMPRODUCT(((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10=" ZZ"))*(D1:D10))/SUMPRODUCT((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ "))

Lars-Åke

This works. Thanks to all 3 of you for all your help.


--
James.


"Lars-Ã…ke Aspelin" wrote:

On Wed, 22 Jul 2009 13:07:11 GMT, Lars-Ã…ke Aspelin
wrote:

On Wed, 22 Jul 2009 06:00:01 -0700, James
wrote:

Hi Luke

Doesn't matter which result it returns as it'll always be the same e.g. in
your example both rows would just show 22 rather than 22 and 25 as you have
done. ZZ will never appear in two columns on the same row only once.

P.S. Thanks for your advice on the UDF, bit beyond my knowledge but I'll
give it a go over the next few days ;)


This formula will take care of the possibility that "ZZ" is found on
more than one row.

=SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")0)*(D1: D10))/SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")0))

Hope this helps / Lars-Ã…ke


And if you know that ZZ is never found on more than one column on any
row, the formula can be shortened a bit, like this:

=SUMPRODUCT(((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10=" ZZ"))*(D1:D10))/SUMPRODUCT((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ "))

Lars-Ã…ke