+ Reply to Thread
Results 1 to 3 of 3

Match Index problem with 2 sets of data to search through

  1. #1
    Registered User
    Join Date
    04-10-2019
    Location
    hull, england
    MS-Off Ver
    2007
    Posts
    2

    Match Index problem with 2 sets of data to search through

    I have the following code in an excel spreadsheet to search through a list of text (AA5:AA30), from the bottom of the range and then search up, for the exact text (located in C3). When it finds the match It will then post the number from the adjoining list/cell (AC5:AC30). I have dragged this code down 5 rows so as to repeat this process but each time ignoring the last result so there is no repetition. In effect it will create a list of the last 5 results matched.

    And it works absolutly fine but...

    I have since created a second column of data to search through (AH4:AH30) and return the result from its own adjoining list of numbers (AJ4:AJ30). The problem is that I don't know how to extend the formula to perform the operation through the second set of data.

    Help would be gratefully received and many thanks in advance

    Iv tried playing around with extending the formula and googling an answer but have no had success.

    This is the existing formula
    =INDEX(AC$5:AC$30, LARGE(IF(ISNUMBER(MATCH(AA$5:AA$30, $C$3, 0)), MATCH(ROW(AA$5:AA$30), ROW(AA$5:AA$30)), ""), ROWS(I$16:$I16)))

    in efffect the new code I want to include will be
    INDEX(AJ$5:AJ$30, LARGE(IF(ISNUMBER(MATCH(AH$5:AH$30, $C$3, 0)), MATCH(ROW(AH$5:AH$30), ROW(AH$5:AH$30)), ""), ROWS(I$16:$I16)))

    but I am unable to "merge" the two formulas

  2. #2
    Forum Expert
    Join Date
    11-27-2007
    Location
    New Jersey, USA
    MS-Off Ver
    2013
    Posts
    1,669

    Re: Match Index problem with 2 sets of data to search through

    If I understand correctly, You are searching for text in C3 and you want to look through two lists [AA$5:AA$30 and AH$5:AH$30] and wherever it finds this text, return value from adjoining cell in columns AC or AJ.

    You can merge the two formulas:
    HTML Code: 
    1. Click on the * Add Reputation if you think this helped you
    2. Mark your thread as SOLVED when question is resolved

    Modytrane

  3. #3
    Registered User
    Join Date
    04-10-2019
    Location
    hull, england
    MS-Off Ver
    2007
    Posts
    2

    Re: Match Index problem with 2 sets of data to search through

    That works, thankyou very much
    Last edited by cincinnati_kid; 04-10-2019 at 06:42 PM.

+ 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. [SOLVED] Index/Match Based on Two Data Sets
    By KoolKatelyn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-25-2019, 04:09 PM
  2. How to optimize Index Match , for large sets of data
    By asweare in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 12-29-2018, 12:46 PM
  3. [SOLVED] INDEX and MATCH with structured references and from two data sets
    By Hannah12 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-07-2018, 11:53 AM
  4. Index and Match formulas for three different data sets
    By dksodhi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-16-2017, 03:59 AM
  5. [SOLVED] Search by cell value index-match save problem
    By kunkka in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2016, 10:14 AM
  6. [SOLVED] How to optimise INDEX(MATCH()) for large data sets?
    By Dan155 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2015, 11:03 PM
  7. Index Match Rows for multiple data sets
    By Martin Chamberlin in forum Excel General
    Replies: 1
    Last Post: 05-23-2014, 07:07 AM

Tags for this Thread

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