Vlookup off multiple columns
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. |
Vlookup off multiple columns
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. |
Vlookup off multiple columns
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 |
Vlookup off multiple columns
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. |
Vlookup off multiple columns
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. |
Vlookup off multiple columns
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. |
Vlookup off multiple columns
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 |
Vlookup off multiple columns
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 |
Vlookup off multiple columns
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?! |
Vlookup off multiple columns
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 |
Vlookup off multiple columns
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 |
Vlookup off multiple columns
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?! |
Vlookup off multiple columns
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 |
Vlookup off multiple columns
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 |
Vlookup off multiple columns
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 |
All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com