Partial lookup issue

[blopez5518]

Hello Everyone,

I was hoping someone could help me with a formula for a similar situation to what I have below. I know I can use an index/match formula to match B2 to column A and pull "The Snow is white". But I can not seem to find a way to do it the other way around.


[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="width: 115"]The sky is blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]white[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 293"]
<tbody>[TR]
[TD="width: 293"]does anything in A2 match any cell in Column B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="width: 115"]The grass is green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]blue[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="width: 115"]The snow is white[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pgc01]

Hi
Welcome to the board

Try in C1:

=LOOKUP(2,1/SEARCH($B$1:$B$3,A1),$B$1:$B$3)

Copy down

 

[blopez5518]

You are amazing, that worked perfectly!

 

[pgc01]

I'm glad it helped.