+ Reply to Thread
Results 1 to 2 of 2

How to search within a dynamic range

  1. #1
    Registered User
    Join Date
    04-28-2020
    Location
    Brussels
    MS-Off Ver
    2010
    Posts
    1

    How to search within a dynamic range

    Hi all, I have the following problem to solve using Excel.

    I have a list of sample numbers in one Excel file ('file 1' in attached file).

    I have to couple each sample identifier from file 1 (cells in gray) to information from another sample of the same person ('file 2'). The information I need should be imported in columns C and D (which has been partially filled in for clarity).

    The information from file 2 that I need concerns 1) the test outcome (column I), and 2) the test Date (column G), that belongs to the same person (column F) as the sample identifier belongs to. However, the information I need is NEVER on the same row as the gray sample identifiers, but must be extracted from columns G & I on the row where either 'Positive' or 'Negative' is in column H.

    For instance, for sample 2 (and 11 and 3) I need 'Negative' from position I22 and the corresponding date in G22. For Sample 16, I need 'Negative' from I11 and date from G11.

    I know how to retrieve cell content from a given relative position using INDEX and MATCH functions:
    =INDEX('K:\[file1.xlsx]Tab1'!$A$1:$B$10000;MATCH(A1;'K:\file2.xlsx]Tab2'!$A$1:$A$10000;0);2)

    However, the difficulty here is that I need to lookup for every sample identifier, the test outcome and date from the row where test outcome (column I) is NOT "-" for the SAME person, given in column F.

    An extra difficulty would be that for person A there are multiple rows where there is not "-" in column I, and I'd need in different columns both of the test outcomes (I5 and I9) and both the dates (G5 and G9).

    Any help about how to approach this is much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,955

    Re: How to search within a dynamic range

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    Note: As you are new I will add it for you.Please read forum rules which you might have missed

    https://www.mrexcel.com/board/thread...range.1132326/

+ 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. problem referencing a dynamic range for a search function. *File Attached*
    By unit285 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-31-2015, 04:08 PM
  2. paste static range into dynamic location with search
    By plague5050 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2014, 09:12 PM
  3. Match ComboBox with dynamic range, then add Textbox1 to dynamic range
    By Lasse Moe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-07-2014, 01:26 AM
  4. Replies: 1
    Last Post: 07-24-2013, 12:13 PM
  5. using offset function to create dynamic range based on a match search
    By freddiethomas in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-08-2013, 05:34 AM
  6. using offset function to create dynamic range based on a match search
    By freddiethomas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-08-2013, 04:14 AM
  7. [SOLVED] search a text cell CONTAINING matching text in a dynamic range (not exact match)
    By msgoldcup@gmail.com in forum Excel General
    Replies: 4
    Last Post: 04-02-2012, 07:37 AM

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