+ Reply to Thread
Results 1 to 3 of 3

Search with multiply results

  1. #1
    Registered User
    Join Date
    10-26-2016
    Location
    Australia
    MS-Off Ver
    excel 2016
    Posts
    34

    Search with multiply results

    Hi,

    I'm trying to work out the best way to search and display all partial matches related to one cell. What would be the best way to achieve this. I tried xlookup and could only get one result and lots of errors. I did look at index match and had no luck either.

    In the attached file "Find me" B9 is the partial number. Looking in "Data" Column "B" and then transferring results from "Data" A:B into "Find me" D:E columns.

    Any help would be much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,700

    Re: Search with multiply results

    I've used a helper column in the Data sheet to identify matching records and to give each a unique serial number. Put this formula in H2 of that sheet:

    =IF(LEFT(B2,LEN('Find Me'!B$9))='Find Me'!B$9,MAX(H$1:H1)+1,"-")

    then copy down to the bottom of your data (i.e. to H3181). The hyphens help to show where the formula is active.

    You can put this formula in B10 of the Find Me sheet:

    =MAX(Data!H:H)&" matching records found"

    to tell you how many matching records there are, and then use these formulae in the cells stated on the Find Me sheet:

    D10: =IF(ROWS($1:1)>MAX(Data!$H:$H),"",INDEX(Data!$B:$B,MATCH(ROWS($1:1),Data!$H:$H,0)))

    E10: =IF(D10="","",INDEX(Data!$A:$A,MATCH(ROWS($1:1),Data!$H:$H,0)))

    You need to copy these two formulae down for as many rows as stated in cell B10 (it doesn't matter if you copy too far, as you will just get blanks).

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-26-2016
    Location
    Australia
    MS-Off Ver
    excel 2016
    Posts
    34

    Re: Search with multiply results

    Thanks Pete that worked well.

    How would i go about making the "Find Me" D10:D3181 to be in ascending order?

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 01-13-2019, 01:46 PM
  2. Multiply, adding and subtracting VLookup results
    By MattKoleczko in forum Excel General
    Replies: 5
    Last Post: 11-29-2016, 07:17 PM
  3. [SOLVED] Need help with existing macro to multiply results
    By velocitygraphix in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-05-2014, 11:31 AM
  4. How to: Macro to divide/multiply and show results
    By Viktor86HUN in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-20-2013, 06:52 AM
  5. Multiply by percentage and round final results
    By jeffreybrown in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-04-2012, 05:51 PM
  6. Sumproduct Help-multiply the results
    By Merlin54k in forum Excel General
    Replies: 2
    Last Post: 06-07-2006, 04:40 PM
  7. [SOLVED] multiply A by 1000 record results in B
    By Mother Goose in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-25-2006, 04:40 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1