Searching Table and return first 10 matched results

[gamenano]

Hi,

I need help with formulas that will return search results based on keywords. I wanted to use this application for household inventories. So the idea is if I needed an iPhone cable, I just type "iPhone cable" and it will list the first 10 row numbers matching that keyword. I used the match function to get the first match, but not sure how to ignore the first match found and continue to look down on the table to find the second match, then the 3rd and so forth.

Thanks for your help guy. Sample data included.

[mehmetcik]

The Match Function will not help you.

You need to use an Array Formula

Let me look at your sample Spreadsheet.

Formula for B4
=IF(SEARCH($C$2,Table1[Product Name])>0,ROW(Table1[SKU])-1,999)

You probably need to enter the formula using ctrl Shift Enter

[Pete_UK]

I see that you have Ariston in both the Product Name and the Description columns of your table. Which of these columns do you want us to search through looking for a match with the keyword?

Pete

[Glenn Kennedy]

In B4:
=IF(C5="","",ROWS($4:4))

and in C4:
=IFERROR(INDEX(Table!C:C,AGGREGATE(15,6,ROW(Table1[Product Name])/(ISNUMBER(SEARCH($C$2,Table1[Product Name]))),ROWS($1:1))),"")

both copied down.

[gamenano]

I see that you have Ariston in both the Product Name and the Description columns of your table. Which of these columns do you want us to search through looking for a match with the keyword?

Pete

Hi Pete, thank you for your reply, the search function is for the product name only.

[Pete_UK]

A slightly different approach, which works in my old version of Excel (and will also work in newer versions):

Put this formula in cell E2 of the Table sheet:

=IF(ISNUMBER(SEARCH(Sheet2!$C$2,B2)),MAX(E$1:E1)+1,"-")

Copy this down beyond your list of items, to allow for new data being added (the hyphens help to show where the formula is active).

Then in Sheet2 you can use these formulae in the cells stated:

B4: =IF(ROWS($1:1)>MAX(Table!$E:$E),"",ROWS($1:1))

C4: =IFERROR(INDEX(Table!$B:$B,MATCH(ROWS($1:1),Table!$E:$E,0)),"")

Copy these down as far as you need them (10 rows).

Hope this helps.

Pete

[gamenano]

Thank you mehmetcik!

[Pete_UK]

Thank you for the rep - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete