partial string lookup across multiple columns

[goong522]

	a	b	c	d	e	f
1	dog	dog1	dog 2	dogs	Dogs
2	cat	cats	cat1	cat 2	Cat
3	hamster	hamsters	hamster1	hamster 2	hamster
4

<tbody>
</tbody>

hi all - Happy New Year.
I was hoping the Excel guru's on this forum can assist me with my issue.

I currently have the above worksheet. Table would be defined as A1:D3. I'm looking for a formula to put in cell F1 that will take the value of cell E1 (which is "Dogs"), and look for a partial match in table A1:D3 (look for any instance that might have "Dogs", an return the corresponding value of column A. So looking for cell E2- instances of "Dogs", it would cell D1 ("dogs") and return cell A1 - "dog"

thank you in advance for the help!

 

[Aladin Akyurek]

In E1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($A1:$C1,$D1),$A1:$C1),"not found")

 

[goong522]

In E1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($A1:$C1,$D1),$A1:$C1),"not found")

Thanks for suggestion Aladin. But I am using cell E1 as the value to search for. So looking for "Dogs", it would be found in D1 and return cell A1 - "dog". The formula above just returns the value that I am searching for.

 

[goong522]

	a	b	c	d	e - search for	f - desired return
1	dog	dog1	dog 2	dogs	Dogs	dog
2	cat	cats	cat1	cat 2	hamsters	hamster
3	hamster	hamsters	hamster1	hamster 2	cat	cat
4	dog	dog 3	dog4	dog5	Dog	dog

<tbody>
</tbody>

hi all - Happy New Year.
I was hoping the Excel guru's on this forum can assist me with my issue.

I currently have the above worksheet. Table would be defined as A1:D3. I'm looking for a formula to put in cell F1 that will take the value of cell E1 (which is "Dogs"), and look for a partial match in table A1:D3 (look for any instance that might have "Dogs", an return the corresponding value of column A. So looking for cell E2- instances of "Dogs", it would cell D1 ("dogs") and return cell A1 - "dog"

*** should have also mentioned that I would want to search for E1 - Dogs across A1:D3. I've updated the table I initially posted to show search values and desired results. hopefully this helps clarify my issue.

thank you in advance for the help!

small amendment for clarification

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	dog	dog1	dog 2	dogs	Dogs	dogs
2	cat	cats	cat1	cat 2	Cat	cat
3	hamster	hamsters	hamster1	hamster 2	hamster	hamster
Sheet1

In F1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($A1:$D1,$E1),$A1:$D1),"not found")

 

[goong522]

	a	b	c	d	e - search for	f - desired return
1	dog	dog1	dog 2	dogs	Dogs	dog
2	cat	cats	cat1	cat 2	hamsters	hamster
3	hamster	hamsters	hamster1	hamster 2	cat	cat

<tbody>
</tbody>

Sheet1
​

In F1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH($A1:$D1,$E1),$A1:$D1),"not found")

Thanks again Aladin! I reposted an amendment to my initial spreadsheet an what I'm looking to accomplish.
Looking to search for value in E1 - Dogs across A1:D3, and if found, return the corresponding value in column A - which would be "dog" in my example.

 

[Aladin Akyurek]

	a	b	c	d	e - search for	f - desired return
1	dog	dog1	dog 2	dogs	Dogs	dog
2	cat	cats	cat1	cat 2	hamsters	hamster
3	hamster	hamsters	hamster1	hamster 2	cat	cat

<tbody>
</tbody>

Thanks again Aladin! I reposted an amendment to my initial spreadsheet an what I'm looking to accomplish.
Looking to search for value in E1 - Dogs across A1:D3, and if found, return the corresponding value in column A - which would be "dog" in my example.

Are we looking for E1 in A1:D1 or A1:D3 or B1:D1 or B1:D3 and always a result from A1:A3?

 

[goong522]

Are we looking for E1 in A1:D1 or A1:D3 or B1:D1 or B1:D3 and always a result from A1:A3?

Aladin - we are looking for E1 in range A1:D3, and return result in A1:A3.

 

[Aladin Akyurek]

Aladin - we are looking for E1 in range A1:D3, and return result in A1:A3.

Book1
	A	B	C	D	E	F
1	dog	dog1	dog 2	dogs	Dogs	dog
2	cat	cats	cat1	cat 2	hamsters	hamster
3	hamster	hamsters	hamster1	hamster 2	cat	cat
Sheet1

In F1 control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,1/MMULT(ISNUMBER(SEARCH(E1,$A$1:$D$3))+0,TRANSPOSE(COLUMN($A$1:$D$3)^0)),$A$1:$A$3)

 

[goong522]

	A	B	C	D	E	F
1	dog	dog1	dog 2	dogs	Dogs	dog
2	cat	cats	cat1	cat 2	hamsters	hamster
3	hamster	hamsters	hamster1	hamster 2	cat	cat

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

In F1 control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,1/MMULT(ISNUMBER(SEARCH(E1,$A$1:$D$3))+0,TRANSPOSE(COLUMN($A$1:$D$3)^0)),$A$1:$A$3)

THANK YOU SO MUCH ALADIN! IT WORKS!
I have a follow up question - my possible table (which is A1:D3 in my example) will most likely expand to A1:D2000. when I try to adjust the formula you posted above, I get a lot of mismatches (incorrect results). and is there a way to add something to the formula so if no matches (or partial matches) are found, it would result in "no matches"?