Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Hi, I am using the VLOOKUP function to find a specified value in a column and
then to return another value/text in a onother column but in tha same row. However, now I want to extend my search of the specified value to include a search among several columns and when it find a match again return a value/text associated with this value. How do I do I write a formula for this? All the best J |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Saved from a few previous posts:
=index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Jonas wrote: Hi, I am using the VLOOKUP function to find a specified value in a column and then to return another value/text in a onother column but in tha same row. However, now I want to extend my search of the specified value to include a search among several columns and when it find a match again return a value/text associated with this value. How do I do I write a formula for this? All the best J -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
"Dave Peterson" wrote: Saved from a few previous posts: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Jonas wrote: Hi, I am using the VLOOKUP function to find a specified value in a column and then to return another value/text in a onother column but in tha same row. However, now I want to extend my search of the specified value to include a search among several columns and when it find a match again return a value/text associated with this value. How do I do I write a formula for this? All the best J -- Dave Peterson Hi and Thanks for the input. However, i cant get it to work as I want, perhaps depending on a bad description of the problem from me. I have in sheet1 a cell with a number, which I want to match with the same number in sheet2. However, the number can be found in either column 1, 2 3, 4, etc in sheet 2 and it can only occur once. Whenever a match is found I would like the formula to transfer a number or text associated with the found number but in a different cell (but of course the same row) in sheet2. Would the given formula decribed by you earlier fix this? All the best Jonas |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
I think you're going to have to check each column of sheet2:
=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),VLOOKUP(A1,Sh eet2!A:G,5,FALSE), IF(ISNUMBER(MATCH(A1,Sheet2!B:B,0)),VLOOKUP(A1,She et2!B:G,4,FALSE), IF(ISNUMBER(MATCH(A1,Sheet2!C:C,0)),VLOOKUP(A1,She et2!C:G,3,FALSE), "Missing"))) (I think...) The bad news is that etc portion. You can only have 7 nested levels. You may find something like this that concatenates a bunch of strings: =IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),"",VLOOKUP(A1, Sheet2!A:G,5,FALSE)) &IF(ISERROR(MATCH(A1,Sheet2!B:B,0)),"",VLOOKUP(A1, Sheet2!B:G,4,FALSE)) &IF(ISERROR(MATCH(A1,Sheet2!C:C,0)),"",VLOOKUP(A1, Sheet2!C:G,3,FALSE)) This just returns empty strings when there isn't a match--but when it finds one, it returns that other cell. And since you said that there is a unique location, it should work. If you're returning a number, then you won't want to concatenate text: =IF(ISERROR(MATCH(A1,Sheet2!A:A,0)),0,VLOOKUP(A1,S heet2!A:G,5,FALSE)) +IF(ISERROR(MATCH(A1,Sheet2!B:B,0)),0,VLOOKUP(A1,S heet2!B:G,4,FALSE)) +IF(ISERROR(MATCH(A1,Sheet2!C:C,0)),0,VLOOKUP(A1,S heet2!C:G,3,FALSE)) Jonas wrote: "Dave Peterson" wrote: Saved from a few previous posts: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0)) (all in one cell) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can't use the whole column. This returns the value in othersheet column C when column A and B (of othersheet) match A2 and B2 of the sheet with the formula. And you can add more conditions by just adding more stuff to that product portion of the formula: =index(othersheet!$d$1:$d$100, match(1,(a2=othersheet!$a$1:$a$100) *(b2=othersheet!$b$1:$b$100) *(c2=othersheet!$c$1:$c$100),0)) Jonas wrote: Hi, I am using the VLOOKUP function to find a specified value in a column and then to return another value/text in a onother column but in tha same row. However, now I want to extend my search of the specified value to include a search among several columns and when it find a match again return a value/text associated with this value. How do I do I write a formula for this? All the best J -- Dave Peterson Hi and Thanks for the input. However, i cant get it to work as I want, perhaps depending on a bad description of the problem from me. I have in sheet1 a cell with a number, which I want to match with the same number in sheet2. However, the number can be found in either column 1, 2 3, 4, etc in sheet 2 and it can only occur once. Whenever a match is found I would like the formula to transfer a number or text associated with the found number but in a different cell (but of course the same row) in sheet2. Would the given formula decribed by you earlier fix this? All the best Jonas -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |