Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup do a multi-column match...if not then what
I want to do a multi column lookup.
If I Match" Bob with 2, I would get b John with 2, I would get e Will vlookup do this? Col1 Col2 Col3 Bob 1 a Bob 2 b Bob 3 c John 1 d John 2 e John 3 f |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup do a multi-column match...if not then what
Insert a new column, then concatenate the data that is in the next two columns.
Example: If you insert a new column A, to concatenate the data that is in columns B and C, row 2, enter the formula: =B2&" "&C2 Copy the formula down and convert it to values via Copy | Paste-Special ... Values. Now you can do a VLookUp on the new column A to return the data. Good Luck. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup do a multi-column match...if not then what
Dave,
You would use index - match for that. try this array formula (see below) In practice i'd use cell references for Bob & 3 =INDEX(C1:C6,MATCH(1,(A1:A6="Bob")*(B1:B6=3),0)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Dave" wrote: I want to do a multi column lookup. If I Match" Bob with 2, I would get b John with 2, I would get e Will vlookup do this? Col1 Col2 Col3 Bob 1 a Bob 2 b Bob 3 c John 1 d John 2 e John 3 f |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup do a multi-column match...if not then what
Thanks that's a winner
"Mike H" wrote: Dave, You would use index - match for that. try this array formula (see below) In practice i'd use cell references for Bob & 3 =INDEX(C1:C6,MATCH(1,(A1:A6="Bob")*(B1:B6=3),0)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Dave" wrote: I want to do a multi column lookup. If I Match" Bob with 2, I would get b John with 2, I would get e Will vlookup do this? Col1 Col2 Col3 Bob 1 a Bob 2 b Bob 3 c John 1 d John 2 e John 3 f |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup do a multi-column match...if not then what
Great example - I was able to copy/modify the equation below and get it to work. But I don't completely understand why it does work? Specifically, what does the "1" do? MATCH(1,. I've tried looking in the excel help and think I understand how to do a Match function, but can't understand what the "1" is doing in the above/below formula. I do know if I change the "1" to a "2" or anything else, it doesn't work. Thanks in advance for the help. Scott Smith "Mike H" wrote: Dave, You would use index - match for that. try this array formula (see below) In practice i'd use cell references for Bob & 3 =INDEX(C1:C6,MATCH(1,(A1:A6="Bob")*(B1:B6=3),0)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Dave" wrote: I want to do a multi column lookup. If I Match" Bob with 2, I would get b John with 2, I would get e Will vlookup do this? Col1 Col2 Col3 Bob 1 a Bob 2 b Bob 3 c John 1 d John 2 e John 3 f |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup do a multi-column match...if not then what
Hi Scott
Maybe this will be helpfull to you. Select the cell with your formula then press and hold the Alt button while you press the letters TUF . You should see the Evaluate function menu, press the evaluate button and see when its avaluating the match function, it converts all the logical True/False to 1 and 0 . Its very usefull when working out problems with formulas. HTH John "Scott Smith" wrote in message ... Great example - I was able to copy/modify the equation below and get it to work. But I don't completely understand why it does work? Specifically, what does the "1" do? MATCH(1,. I've tried looking in the excel help and think I understand how to do a Match function, but can't understand what the "1" is doing in the above/below formula. I do know if I change the "1" to a "2" or anything else, it doesn't work. Thanks in advance for the help. Scott Smith "Mike H" wrote: Dave, You would use index - match for that. try this array formula (see below) In practice i'd use cell references for Bob & 3 =INDEX(C1:C6,MATCH(1,(A1:A6="Bob")*(B1:B6=3),0)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Dave" wrote: I want to do a multi column lookup. If I Match" Bob with 2, I would get b John with 2, I would get e Will vlookup do this? Col1 Col2 Col3 Bob 1 a Bob 2 b Bob 3 c John 1 d John 2 e John 3 f |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup do a multi-column match...if not then what
In this expression:
MATCH(1,(A1:A6="Bob")*(B1:B6=3),0) A1:A6="Bob" will result in an array of 6 True/Falses (depending on the values). B1:B6=3 will result in an array of 6 true/falses, too. When you multiply those two arrays, you end up with an array of 6 1's or 0's (true*true = 1, false*anything =0) So the match(1,(array of 1's and 0's), 0) will return the position of the first 1 (true*true). (the 0 means =match() is looking for an exact match). In this expression, it'll be the row number of the first match where column A and column B are Bob and 3. Scott Smith wrote: Great example - I was able to copy/modify the equation below and get it to work. But I don't completely understand why it does work? Specifically, what does the "1" do? MATCH(1,. I've tried looking in the excel help and think I understand how to do a Match function, but can't understand what the "1" is doing in the above/below formula. I do know if I change the "1" to a "2" or anything else, it doesn't work. Thanks in advance for the help. Scott Smith "Mike H" wrote: Dave, You would use index - match for that. try this array formula (see below) In practice i'd use cell references for Bob & 3 =INDEX(C1:C6,MATCH(1,(A1:A6="Bob")*(B1:B6=3),0)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Dave" wrote: I want to do a multi column lookup. If I Match" Bob with 2, I would get b John with 2, I would get e Will vlookup do this? Col1 Col2 Col3 Bob 1 a Bob 2 b Bob 3 c John 1 d John 2 e John 3 f -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can vlookup do a multi-column match...if not then what
Excellent answer, very helpful.
I am in awe of the in-depth excel knowledge you guys have. Thanks again. "Dave Peterson" wrote: In this expression: MATCH(1,(A1:A6="Bob")*(B1:B6=3),0) A1:A6="Bob" will result in an array of 6 True/Falses (depending on the values). B1:B6=3 will result in an array of 6 true/falses, too. When you multiply those two arrays, you end up with an array of 6 1's or 0's (true*true = 1, false*anything =0) So the match(1,(array of 1's and 0's), 0) will return the position of the first 1 (true*true). (the 0 means =match() is looking for an exact match). In this expression, it'll be the row number of the first match where column A and column B are Bob and 3. Scott Smith wrote: Great example - I was able to copy/modify the equation below and get it to work. But I don't completely understand why it does work? Specifically, what does the "1" do? MATCH(1,. I've tried looking in the excel help and think I understand how to do a Match function, but can't understand what the "1" is doing in the above/below formula. I do know if I change the "1" to a "2" or anything else, it doesn't work. Thanks in advance for the help. Scott Smith "Mike H" wrote: Dave, You would use index - match for that. try this array formula (see below) In practice i'd use cell references for Bob & 3 =INDEX(C1:C6,MATCH(1,(A1:A6="Bob")*(B1:B6=3),0)) This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correctly then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Dave" wrote: I want to do a multi column lookup. If I Match" Bob with 2, I would get b John with 2, I would get e Will vlookup do this? Col1 Col2 Col3 Bob 1 a Bob 2 b Bob 3 c John 1 d John 2 e John 3 f -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup/match to find column name | Excel Worksheet Functions | |||
How to use index match for multi lookups? | Excel Worksheet Functions | |||
match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
Vlookup? to match column in two sheets | Excel Discussion (Misc queries) | |||
is there a way to search with vlookup to match more than 1 column | Excel Discussion (Misc queries) |