+ Reply to Thread
Results 1 to 6 of 6

selecting second result while using index and match formula.

  1. #1
    Registered User
    Join Date
    11-20-2019
    Location
    nepal
    MS-Off Ver
    office 2016
    Posts
    21

    selecting second result while using index and match formula.

    while using the index and match formula, if the first result is not satisfied how to apply for the second result.
    to make my question clear please check the included excel file.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: selecting second result while using index and match formula.

    Maybe this in F2 and down?
    =INDEX($B$1:$B$7,AGGREGATE(15,6,(ROW($B$2:$B$7))/((E2=$A$2:$A$7)*($B$2:$B$7="found")),1))

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: selecting second result while using index and match formula.

    In F2 then Copied across.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Registered User
    Join Date
    11-20-2019
    Location
    nepal
    MS-Off Ver
    office 2016
    Posts
    21

    Re: selecting second result while using index and match formula.

    thanks for the effort, i got to learn new things.
    but this was not what i was looking for.

    what i just wanted is,
    while using index and match it searches in top to bottom method. so, while i use the formula "=index("result_array",match("20300192",$A$2:$A$7,0)).
    in $A$2:$A$7 contains two same values "20300192". where first row of "result array" contains the value "not found" and second-row contains some other value,

    so i don't want "not found" but the next row result which has other values but not "not found"

    so, i want to discard the row which contain "not found" only if there are two same values like "20300192". if not then "not found".

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: selecting second result while using index and match formula.

    This is exactly the resolution I gave you.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,587

    Re: selecting second result while using index and match formula.

    The expected result and formula result are same. Pl see file. If any problem pl upload file showing the problem. Pl see my previous post. In F6 you are getting Not Found, because you don't have Found for that number
    20300817 in original data, from the formula.
    Last edited by kvsrinivasamurthy; 11-24-2020 at 01:09 AM.

+ 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 formula not returning expected result
    By lukela85 in forum Excel General
    Replies: 4
    Last Post: 12-18-2017, 11:23 AM
  2. [SOLVED] Odd result from index/match formula
    By iantix in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-18-2017, 06:35 AM
  3. [SOLVED] IF INDEX MATCH based on result of formula
    By SLIM512 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-01-2016, 09:46 AM
  4. [SOLVED] Index, Match formula returns #NA error if result is from an average formula.
    By billrogers184 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-28-2016, 11:43 AM
  5. [SOLVED] Index - Match formula not giving me an expected result
    By longbow007 in forum Excel General
    Replies: 5
    Last Post: 11-25-2015, 07:34 PM
  6. [SOLVED] Formula whit INDEX and MATCH. I try to make this formula to result.
    By Anka in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-28-2012, 06:26 PM
  7. hide zeros and NA#'s from result of Index/match formula
    By merlyn45 in forum Excel General
    Replies: 2
    Last Post: 05-10-2012, 02:10 PM

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