partial string lookup across multiple columns

[goong522]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e
[/TD]
[TD]f
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]dog
[/TD]
[TD]dog1
[/TD]
[TD]dog 2
[/TD]
[TD]dogs
[/TD]
[TD]Dogs
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]cat
[/TD]
[TD]cats
[/TD]
[TD]cat1
[/TD]
[TD]cat 2
[/TD]
[TD]Cat
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]hamster
[/TD]
[TD]hamsters
[/TD]
[TD]hamster1
[/TD]
[TD]hamster 2
[/TD]
[TD]hamster
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e - search for
[/TD]
[TD]f - desired return
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]dog
[/TD]
[TD]dog1
[/TD]
[TD]dog 2
[/TD]
[TD]dogs
[/TD]
[TD]Dogs
[/TD]
[TD]dog
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]cat
[/TD]
[TD]cats
[/TD]
[TD]cat1
[/TD]
[TD]cat 2
[/TD]
[TD]hamsters
[/TD]
[TD]hamster
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]hamster
[/TD]
[TD]hamsters
[/TD]
[TD]hamster1
[/TD]
[TD]hamster 2
[/TD]
[TD]cat
[/TD]
[TD]cat
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]dog
[/TD]
[TD]dog 3
[/TD]
[TD]dog4
[/TD]
[TD]dog5
[/TD]
[TD]Dog
[/TD]
[TD]dog
[/TD]
[/TR]
</tbody>[/TABLE]
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]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e - search for
[/TD]
[TD]f - desired return
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]dog
[/TD]
[TD]dog1
[/TD]
[TD]dog 2
[/TD]
[TD]dogs
[/TD]
[TD]Dogs
[/TD]
[TD]dog
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]cat
[/TD]
[TD]cats
[/TD]
[TD]cat1
[/TD]
[TD]cat 2
[/TD]
[TD]hamsters
[/TD]
[TD]hamster
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]hamster
[/TD]
[TD]hamsters
[/TD]
[TD]hamster1
[/TD]
[TD]hamster 2
[/TD]
[TD]cat
[/TD]
[TD]cat
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[TD]e - search for
[/TD]
[TD]f - desired return
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]dog
[/TD]
[TD]dog1
[/TD]
[TD]dog 2
[/TD]
[TD]dogs
[/TD]
[TD]Dogs
[/TD]
[TD]dog
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]cat
[/TD]
[TD]cats
[/TD]
[TD]cat1
[/TD]
[TD]cat 2
[/TD]
[TD]hamsters
[/TD]
[TD]hamster
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]hamster
[/TD]
[TD]hamsters
[/TD]
[TD]hamster1
[/TD]
[TD]hamster 2
[/TD]
[TD]cat
[/TD]
[TD]cat
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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
dog	dog1	dog 2	dogs	Dogs	dog
cat	cats	cat1	cat 2	hamsters	hamster
hamster	hamsters	hamster1	hamster 2	cat	cat

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

</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"?