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
|