Triple lookup?

A

Andy the Yeti

Hi,

Thanks for any help or direction on this question. I have an excel
spreadsheet with a dozen plus columns.

I would like to be able to create a lookup formula of sorts that will based
on meeting three values within the same row. If these three value are met
then the lookup could return the value of say a specified cell again within
the same row.

Its basically a vlookup but instead of meeting one it would meet three values.

Many thanks for any help!
Andy
 
A

Andy the Yeti

Sorry hope this example helps
A B C D G H
1 Fred 48 DD Car Blue 700
2 Bob 45 FF Car Red 600
3 John 63 FF Car Green 500
4 George 54 AA Bike Black 500
5 Ralf 67 BB Car Red 500
6 Bill 43 CC Car Blue 600
7 Charles 55 CC Bike Yellow 600

I would like to lookup for example in Row 3, if it meets the positive
lookups of 63 car and green I would like it to return John.

Thanks
 
S

Stefi

This formula returns "John" if you searches these values: 63 car and green
=IF(OR(ISERROR(VLOOKUP(63,B:B,1,FALSE)),ISERROR(VLOOKUP("Car",D:D,1,FALSE)),ISERROR(VLOOKUP("Green",E:E,1,FALSE))),"Not Found",INDEX(A:A,MATCH(63,B:B,0)))


But it's still not clear where do you store data to be searched! Are they in
certain cells? If so, replace their actual values by cell references!

Regards,
Stefi

„Andy the Yeti†ezt írta:
 
D

Dave Peterson

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

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

shail

Hi Andy,

You can use the formula

=INDEX(A2:A8,MATCH(1,(63=B2:B8)*("Car"=D2:D8)*("Green"=E2:E8),0))

This is an array function so you should press ALT+CTRL+ENTER to execute
the formula.

Hope that helps you.

Thanks

Shail
 
A

Andy the Yeti

Thank you all for your help!!!!!




Dave Peterson said:
Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=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))
Click to expand...
 
Top