+ Reply to Thread
Results 1 to 4 of 4

How to use the index match function to capture several rows with one matching column value

  1. #1
    Registered User
    Join Date
    05-11-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2007
    Posts
    12

    How to use the index match function to capture several rows with one matching column value

    I am trying to capture a set of values from a sheet, which has several rows with the match I am looking for.

    I have used the Index and Match functions to capture the first value in column 2, where column A matches a specific value shown in D1
    with the following:
    =INDEX($A1:$B$8,MATCH($D$1,$A:$A,FALSE),2).

    I 'fiddled' this on line 2 to capture the 2nd value by changing the range of the index function to $A2:$B8 and so on.
    However this 'fudged' approach means that I can get a value where the value in Col A no longer matches D1.

    I have attached an example.

    I know there is better way to achieve what I would like - am just working at too basic a level of Excel.
    Can anyone shine a light on my challenge?

    Hopefully....
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to use the index match function to capture several rows with one matching column v

    Hi,


    you could try in F2: it's an array formula

    Please Login or Register  to view this content.
    You have to finish the input of the formula with Ctrl+Shift+Enter rather than Enter.

    Array formulae are embraced by curlies to indicate that they are arrays {=IFERROR...}

    Do not add the curlies by yourself, just copy the formula and use Ctrl+Shift+Enter.

    In the attachment I did not use IFERROR as I'm a Excel 2000 user: I have to use a little different formula. =IF(ISERROR(....

    Hope it helps
    Attached Files Attached Files
    Last edited by canapone; 06-18-2012 at 05:43 AM.

  3. #3
    Registered User
    Join Date
    05-11-2012
    Location
    Bristol, England
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: How to use the index match function to capture several rows with one matching column v

    Fabulous! I have transcribed it into my worksheet and adjusted the values and it works perfectly.
    Thank you SO much!

    I have not used the IFERROR function before - I will go and look it up.

  4. #4
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: How to use the index match function to capture several rows with one matching column v

    Ciao,

    using IFERROR in order to avoid error feedbacks from the formulae is for sure more efficient (and elegant) than using the long mantra IF(ISERROR(formula),"",formula).

    Anyway you obtain same results using any of the two formulae.

    Greetings
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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