Vlookup off multiple columns

[James]

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

 

[Eduardo]

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

 

[Lars-Åke Aspelin]

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)*(D1100))

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

 

[James]

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.

 

[Luke M]

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)

 

[Eduardo]

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]

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?!

 

[Luke M]

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)

 

[Lars-Åke Aspelin]

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

 

[Lars-Åke Aspelin]

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

 

[James]

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]

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.

 

[Lars-Åke Aspelin]

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)*(D110))/SUMPRODUCT(--((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ")>0))

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

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)*(D110))/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"))*(D110))/SUMPRODUCT((A1:A10="ZZ")+(B1:B10="ZZ")+(C1:C10="ZZ"))

Lars-Åke

 

[James]

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