Lookup a word in a table and retrieve a result..... more details follows...

**elinielsen** · 03-19-2012, 02:27 PM

Hi guys and dolls

I have a data table with several rows and columns (Material Master, MM) and from another Excel-sheet I need to lookup a specific word in the MM. The word must be found in a column containing material description (Fields with multiple words and not in an organised way). The result of a hit (could be more hits, but the first hit is OK) should be returned to my "lookup file"..... Sounds easy??? Not to me

Help is needed

Eli Nielsen

**NBVC** · 03-19-2012, 02:31 PM

So if you are looking up a work that you have entered in Sheet2, A2, and trying to find it in Sheet 1, column B, and want to return corresponding item from column A in Sheet1, then try:

=INDEX('Sheet1'!A:A,MATCH("*"&$A2&"*",'Sheet1'!$B:$B,0))

you can copy down and or across, if needed to get more info.

**elinielsen** · 03-19-2012, 03:54 PM

Hi again

It's so simple it's almost embarrasing... I'm not so familiar with the "&" function, but I think I got it now. Thanks a lot for your quick response.

**NBVC** · 03-19-2012, 03:59 PM

& is an easier alternative to the CONCATENATE function and it reduces number of functions you use (especially usefully to avoid nested functions limit)

"*"&A2&"*" is equivalent to CONCATENATE("*",A2,"*")

**elinielsen** · 03-19-2012, 04:11 PM

Nested ??.... Like a function within a function ??

**NBVC** · 03-19-2012, 04:19 PM

yes, that's what I mean....

Like

=IF(ISNA(MATCH(CONCATENATE(A1,B1),X1:X10,0)),"yes","no")

the IF, ISNA, MATCH and CONCATENATE are nested functions.