Triple lookup?
 

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

Triple lookup?
 

Give an example!
Regards,
Stefi


€žAndy the Yeti€� ezt Ã*rta:

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

Triple lookup?
 

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

Triple lookup?
 

This formula returns "John" if you searches these values: 63 car and green
=IF(OR(ISERROR(VLOOKUP(63,B:B,1,FALSE)),ISERROR(VL OOKUP("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:

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

Triple lookup?
 

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

Andy the Yeti wrote:

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

--

Dave Peterson

Triple lookup?
 

Hi Andy,

You can use the formula

=INDEX(A2:A8,MATCH(1,(63=B2:B8)*("Car"=D2:D8)*("Gr een"=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


Andy the Yeti wrote:
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

Triple lookup?
 

Thank you all for your help!!!!!




"Dave Peterson" wrote:

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

Andy the Yeti wrote:

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

--

Dave Peterson