Results 1 to 4 of 4

Thread: Help with a formula

  1. #1

    Help with a formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    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.


  3. #3
    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))),"")

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •