Lookup text strings from array in cells and return another value !

[x_lringoet]

Hi all,

I would like to search the words from cells F2:H5 in the cells A2:A7 and return the value from cell F1 or G1 or H1
in the cells B2:B5

Can anyone help me with a formule I could use to do this lookup.

Thanks a lot in advance.

Kr,

Levi

 

[Shrikant]

write this formula in B2

=IF(NOT(ISERROR(MATCH(A2,$F$2:$F$6,0))),$F$1,IF(NOT(ISERROR(MATCH(A2,$G$2:$G$6,0))),$G$1,IF(NOT(ISERROR(MATCH(A2,$H$2:$H$6,0))),$H$1,"Match Not Found")))

 

[x_lringoet]

Hi Shrikant,

this would be correct if the text in the cell A2 would be exactly the same text as one of the cells in F2:F6,
but that is not always the case.

write this formula in B2

=IF(NOT(ISERROR(MATCH(A2,$F$2:$F$6,0))),$F$1,IF(NOT(ISERROR(MATCH(A2,$G$2:$G$6,0))),$G$1,IF(NOT(ISERROR(MATCH(A2,$H$2:$H$6,0))),$H$1,"Match Not Found")))

 

[Shrikant]

you can use wild card characters.


Replace A2 in above Formula with

"*"&A2&"*"

=IF(NOT(ISERROR(MATCH("*"&A2&"*",$F$2:$F$6,0))),$F$1,IF(NOT(ISERROR(MATCH("*"&A2&"*",$G$2:$G$6,0))),$G$1,IF(NOT(ISERROR(MATCH("*"&A2&"*",$H$2:$H$6,0))),$H$1,"Match Not Found")))

 

[x_lringoet]

Hi Shrikant,

still the same problem.
Maybe this can help you :

	A	B	C	D	E	F	G	H
1						HW</SPAN>	SW</SPAN>	OTHER</SPAN>
2	USB Stick</SPAN>	Match Not Found</SPAN>				PC</SPAN>	Adobe</SPAN>	Cable</SPAN>
3	Microsoft Office</SPAN>					USB</SPAN>	Microsoft</SPAN>	Tool</SPAN>
4	Cable 3m</SPAN>					Printer</SPAN>	Symantec</SPAN>
5	Microsoft Office</SPAN>					Monitor</SPAN>
6	Crimping tool</SPAN>
7	large monitor</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL span=3><COL><COL><COL></COLGROUP>

 

[Shrikant]

Copy this formula in B2,

=IF(SUM(--(IFERROR(SEARCH($F$2:$F$6,A2),0)))<>0,$F$1,IF(SUM(--(IFERROR(SEARCH($G$2:$G$6,A2),0)))<>0,$G$1,IF(SUM(--(IFERROR(SEARCH($H$2:$H$6,A2),0)))<>0,$H$1,"Match Not Found")))


Press Ctrl+Shift+Enter

 

[XOR LX]

Or, presuming that there'll only ever be precisely one string from F2:H5 which is found in a given description (you don't say which should take precedence if more than one is found, so presume it's not possible):

=INDEX($F$1:$H$1,,SUMPRODUCT(ISNUMBER(SEARCH(" "&$F$2:$H$5&" "," "&A2&" "))*(COLUMN($F$2:$H$5)-MIN(COLUMN($F$2:$H$5))+1)))

which can also be extended to arbitrarily larger search ranges simply by changing the range (A2:H5) and without the necessity of adding an additional clause to the formula for every new column added to that search range.

Regards

 

[x_lringoet]

Hi XOR LX,

this is exactly the formula I was looking for, works fine !

Thanks a lot.

KR,

Levi

 

[XOR LX]

Sure. Glad to help.

Regards