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

#### jeremypyle

##### Board Regular
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

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
=if(or(b2=a3,b3=a3,b4=a3),b2&" "&b3&" "&b4,"Wut goes here?")

was that supposed to work?

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

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)

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

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.

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?

Last edited:
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...
Code:
``````=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)))``````

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

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.

Replies
1
Views
427
Replies
4
Views
717
Replies
2
Views
130
Replies
9
Views
723
Replies
8
Views
345

1,203,326
Messages
6,054,747
Members
444,748
Latest member
knowak87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back