Help with a formula

Hi there

I've attached an example spreadsheet. I have a price list spreadsheet listing about 5000 items. What I want to do in a new sheet is type in the keyword and below that it lists all cells with that keyword. Can anyone help me please?

Insert a blank row at the top of the Price List sheet,

Then in D2 add of Price List sheet add formula:

=IF(ISNUMBER(SEARCH(Find!$B$1,B2)),COUNT($D$1:$D1)+1,"")

copied down. This identifies and counts the matches from your keyword.

Now in the Find sheet in A6 enter:

=IFERROR(INDEX('Price list'!A:A,MATCH(ROWS(A$6:A6),'Price list'!$D:$D,0)),"")

copied down as far as you want and across the 3 columns.

Alternative, single, array-formula. Enter in A6 and copy down and across.

=IFERROR(INDEX('Price List'!A:A,LARGE(IF(ISNUMBER(FIND($B$1,'Price List'!$B$1:$B$100)),ROW('Price List'!$B$1:$B$100)),ROW($A1))),"")

Hi Bob,

Yeah, I didn't suggest the array formula because OP mentioned his/her data is 5000 rows.... which would make the array formula sluggish.