+ Reply to Thread
Results 1 to 4 of 4

Combine INDEX(MATCH with INDIRECT (maybe?)

  1. #1
    Registered User
    Join Date
    12-18-2017
    Location
    SD, CA
    MS-Off Ver
    13
    Posts
    2

    Combine INDEX(MATCH with INDIRECT (maybe?)

    I'm attempting to create a spreadsheet that tracks the progress of a sample going through a lab. I want to have the user enter in a piece of information (say the Sample ID) and return what phase that sample is in.

    In the user interfacing sheet, they enter the Sample ID in cell B1

    Using this formula "A" =MATCH(B1,progress!A1:A29,0), I can return the row number that their sample is in, returned in A6

    Using this formula "B" =INDEX(progress!A1:H1,MATCH(1E+100,progress!A2:H2,1)), I can return the column header that has the cell to the farthest right which contains text in it (say we fill in dates as we go from left to right, farthest right being project=complete), returned in A5

    What I would like to do is integrate the results of formula A into formula B such that the "A2:H2" is populated by the "2" that is returned from formula A

    I've been trying things like this =INDEX(progress!A1:H1,MATCH(1E+100,progress!(INDIRECT("A"&A6&":"&"H"&A6)),1) but something in there is way wrong. Any advice?

    [sheet attached, hopefully]
    Attached Files Attached Files
    Last edited by sma365; 12-19-2017 at 01:47 PM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combine INDEX(MATCH with INDIRECT (maybe?)

    Try

    =INDEX(progress!A1:H1,MATCH(1E+100,INDEX(progress!A1:H29,MATCH(B1,progress!A1:A29,0),0),1))

  3. #3
    Registered User
    Join Date
    12-18-2017
    Location
    SD, CA
    MS-Off Ver
    13
    Posts
    2

    Re: Combine INDEX(MATCH with INDIRECT (maybe?)

    Jonmo1
    Re: Combine INDEX(MATCH with INDIRECT (maybe?)

    Try

    =INDEX(progress!A1:H1,MATCH(1E+100,INDEX(progress!A1:H29,MATCH(B1,progress!A1:A29,0),0),1))


    YES!!! Thank you!!! I was close with a table-type match yesterday but didn't get the sytax quite right. Thank you so much. Exactly what I needed!!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combine INDEX(MATCH with INDIRECT (maybe?)

    You're welcome

+ 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. Instead of INDIRECT() need to have INDEX and MATCH
    By Michael9999 in forum Excel Formulas & Functions
    Replies: 30
    Last Post: 03-28-2017, 05:58 AM
  2. [SOLVED] sum using indirect index match
    By jpbisani in forum Excel General
    Replies: 4
    Last Post: 04-23-2016, 02:29 PM
  3. Combine INDEX with INDIRECT + MATCH FUNCTION
    By LemonOrange in forum Excel General
    Replies: 20
    Last Post: 03-26-2014, 04:52 PM
  4. Where do we combine INDEX and INDIRECT functions?
    By BIJALRADIA in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-01-2013, 05:48 AM
  5. Problem with INDEX, INDIRECT, MATCH, MATCH
    By JO505 in forum Excel General
    Replies: 5
    Last Post: 09-01-2011, 05:51 PM
  6. Index/Indirect/Match Help
    By robertsclark in forum Excel General
    Replies: 2
    Last Post: 04-20-2011, 10:24 AM
  7. Combine INDEX+MATCH functions with INDIRECT formula
    By ABSTRAKTUS in forum Excel General
    Replies: 4
    Last Post: 04-12-2011, 04:16 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