If the text in b1,c1 or d1 is contained in a3, then enter the contained text from b1:d1 into b3

[jeremypyle]

This is too hard for me to figure out. It is part of a much larger problem. I think this, however, will help me solve the rest.

Please place the following into excel:

a3:jeremy
a4:matthew
a5:isaac
a6:jeremypyle

b2:jere
b3:matt
b4:isaac

Can you make a formula for b3? I want it to say this:
If the text from b2, b3, or b4 (jere, matt, or isaac) is contained in cell a3, then enter the contained text into b3

In this situation, jere from cell b2 is contained in a3 (jeremypyle), therefore, I want jere to be displayed in cell b3

The problem I am having with this problem is this:
I want multiple selections of cells (b2,b3,and b4)

If I simply put the following into b3, it would work:
=IF(ISNUMBER(SEARCH($B$1,A3)),B$1, "")

However this isn't good enough. If want something similiar to the following:
=IF(ISNUMBER(SEARCH(or($B$1,$B$2,$B$3),A3)),B$1, "")

Why does this formula not work? I tried to use the "or" formula

Can anyone please help?

 

[Anne Troy]

=if(or(b2=a3,b3=a3,b4=a3),b2&" "&b3&" "&b4,"Wut goes here?")

 

[jeremypyle]

was that supposed to work?

 

[Aladin Akyurek]

This is too hard for me to figure out. It is part of a much larger problem. I think this, however, will help me solve the rest.

Please place the following into excel:

a3:jeremy
a4:matthew
a5:isaac
a6:jeremypyle

b2:jere
b3:matt
b4:isaac

Can you make a formula for b3? I want it to say this:
If the text from b2, b3, or b4 (jere, matt, or isaac) is contained in cell a3, then enter the contained text into b3

In this situation, jere from cell b2 is contained in a3 (jeremypyle), therefore, I want jere to be displayed in cell b3

The problem I am having with this problem is this:
I want multiple selections of cells (b2,b3,and b4)

If I simply put the following into b3, it would work:
=IF(ISNUMBER(SEARCH($B$1,A3)),B$1, "")

However this isn't good enough. If want something similiar to the following:
=IF(ISNUMBER(SEARCH(or($B$1,$B$2,$B$3),A3)),B$1, "")

Why does this formula not work? I tried to use the "or" formula

Can anyone please help?

The location of search strings is confusing: B1:D1 according to the topic title, B2:B4 according to the topic content while the topic content wants formulas in B2:B4.

If the topic title is right:

<TABLE style="WIDTH: 197pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=263><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2531" width=71><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=71> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2911025 class=xl63 width=64>jere</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>matt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>isaac</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>jeremy</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>jere</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>matthew</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>matt</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>isaac</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>isaac</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>jeremypyle</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>jere</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

In B2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH($B$1:$D$1,A3),$B$1:$D$1)

 

[jeremypyle]

Fantastic!! That is exactly what I was after. Sorry for the confusing way of explaining myself. And thank you so much for helping me =)

 

[Aladin Akyurek]

Fantastic!! That is exactly what I was after. Sorry for the confusing way of explaining myself. And thank you so much for helping me =)

You are welcome. Thanks for providing feedback.

 

[jeremypyle]

I have a little addition now to the question

Please place the following into excel:

a4=jeremy
a5=matthew
a6=isaac
a7=jeremypyle

b1=jere
b2=jen

c1=matt
c2=matt

d1=isaac
d2=isaac


How do I make b4 display "jere" using =LOOKUP(9.99999999999999E+307,SEARCH($B$2:$D$2,A4),$B$2:$D$2)

In this example, it will show an error because b2 = jen. But I want it to search both b2 and b1. So that there is an option. I have tried the following formula:

=LOOKUP(9.99999999999999E+307,OR(SEARCH($B$2:$D$2,A4),SEARCH($B$1:$D$1,A4)),$B$2:$D$2)

This doesn't work.

I have also tried:

=LOOKUP(9.99999999999999E+307,SEARCH($B$1:$D$2,A4),$B$1:$D$2)

This also doesn't work.

Can you help on this?

 

[Aladin Akyurek]

I have a little addition now to the question

Please place the following into excel:

a4=jeremy
a5=matthew
a6=isaac
a7=jeremypyle

b1=jere
b2=jen

c1=matt
c2=matt

d1=isaac
d2=isaac


How do I make b4 display "jere" using =LOOKUP(9.99999999999999E+307,SEARCH($B$2:$D$2,A4),$B$2:$D$2)

In this example, it will show an error because b2 = jen. But I want it to search both b2 and b1. So that there is an option. I have tried the following formula:

=LOOKUP(9.99999999999999E+307,OR(SEARCH($B$2:$D$2,A4),SEARCH($B$1:$D$1,A4)),$B$2:$D$2)

This doesn't work.

I have also tried:

=LOOKUP(9.99999999999999E+307,SEARCH($B$1:$D$2,A4),$B$1:$D$2)

This also doesn't work.

Can you help on this?

One way...

Define BigStr as referring to:

=REPT("z",255)

Define BigNum as referring to:

=9.99999999999999E+307

Now we can invoke...

=LOOKUP(BigStr,CHOOSE({1,2,3},"",
    LOOKUP(BigNum,SEARCH($B$1:$D$1,A4),$B$1:$D$1),
    LOOKUP(BigNum,SEARCH($B$2:$D$2,A4),$B$2:$D$2)))

 

[jeremypyle]

You are so brilliant Aladin Akyurek! I can't believe you know how to do that. I am very impressed!! Thanks so much =)

 

[Aladin Akyurek]

You are so brilliant Aladin Akyurek! I can't believe you know how to do that. I am very impressed!! Thanks so much =)

Thanks for the kind feedback.