Excel Search function from list

[arvarr]

Hi
Hoping someone can help me.

I was wondering what formula can i use to in Sheet 2 Cell B2 to lookup the list in Sheet 1 (A1:A6) to give the results.
Eg. Sheet 2 Cell B2, expecting the result to be Ms

Sheet 1
A1 Keywords (Header) B1 Results
A2 Microsoft B2 Ms
A3 Adobe B3 Adobe
A4 Word B4 Word
A5 Excel B5 Excel
A6 Powerpoint B6 Powerpoint

Sheet 2
A1 Description (Header)
A2 This is a Microsoft Product
A3 Adobe expires in a year's time
A4 You have to use MS Word

Thanks.M

 

[Andrzejek]

Sheet 2, Cell B2: [tt]=RIGHT(Sheet1!A2, 2)[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[arvarr]

Hi Andy
What i am trying to achieve is to in sheet 2 b2, it needs to look for the list in Sheet 1 for the keywords and return the results.
Thanks

 

[combo]

An example here seems to complete the first part of your problem. You need to search for word found and pick value in the same row and next to the right column.
Power query (get&transform) query could be another method in excel 2016+.

combo

 

[arvarr]

HI combo
Thanks for the info.
How do i create a filter list?

The formula on the example is =IFERROR(LOOKUP(1E+100,SEARCH(tblFilterList[Filter List],[@Name]),tblFilterList[Filter List]),"")

tblFilterList > I can create the name
but not sure how to create the filter list - [Filter List],[@Name]

Thanks.

 

[combo]

The workbook has two structured tables, one of them is named [tt]tblFilterList[/tt] with column having [tt]Filter List[/tt] header. Arguments in (tblFilterList[Filter List],[@Name]),tblFilterList[Filter List]),"") are standard internal and external references to structured tables.
The references in formula are created automatically, according to table name and column headers.

combo

 

[arvarr]

HI Combo
Where is the original table to be able to create the table?
Thanks.

 

[combo]

Insert>Table, select area, mark that table has headers, in table design tab (available when any table cell is selected) change table name. Note "@" in reference to table cell - this means the same row as for the caller.

combo