Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Excel Search function from list

Excel Search function from list

Excel Search function from list

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


RE: Excel Search function from list

Sheet 2, Cell B2: =RIGHT(Sheet1!A2, 2)

---- Andy

There is a great need for a sarcasm font.

RE: Excel Search function from list

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.

RE: Excel Search function from list

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+.


RE: Excel Search function from list

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]


RE: Excel Search function from list

The workbook has two structured tables, one of them is named tblFilterList with column having Filter List 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.


RE: Excel Search function from list

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

RE: Excel Search function from list

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close