VLOOKUP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jonas - 22 May 2006 12:51 GMT

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

Reply to this Message

Dave Peterson - 22 May 2006 13:25 GMT

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))

> 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.
[quoted text clipped - 5 lines]
> All the best
> J

Signature

Dave Peterson

Reply to this Message

Jonas - 22 May 2006 14:26 GMT

> Saved from a few previous posts:
>
[quoted text clipped - 29 lines]
> > All the best
> > J

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

Reply to this Message

Dave Peterson - 23 May 2006 00:45 GMT

I think you're going to have to check each column of sheet2:

=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),VLOOKUP(A1,Sheet2!A:G,5,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet2!B:B,0)),VLOOKUP(A1,Sheet2!B:G,4,FALSE),
IF(ISNUMBER(MATCH(A1,Sheet2!C:C,0)),VLOOKUP(A1,Sheet2!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,Sheet2!A:G,5,FALSE))
+IF(ISERROR(MATCH(A1,Sheet2!B:B,0)),0,VLOOKUP(A1,Sheet2!B:G,4,FALSE))
+IF(ISERROR(MATCH(A1,Sheet2!C:C,0)),0,VLOOKUP(A1,Sheet2!C:G,3,FALSE))

> > Saved from a few previous posts:
> >
[quoted text clipped - 47 lines]
> All the best
> Jonas

Signature

Dave Peterson

Reply to this Message