+ Reply to Thread
Results 1 to 6 of 6

Nested If Statement in INDEX MATCH

  1. #1
    Registered User
    Join Date
    03-17-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Unhappy Nested If Statement in INDEX MATCH

    Hi

    I'm struggling to get the below to work.

    =IF($E$1=1,INDEX(South!R:R,MATCH('Regions Summary'!K63,South!D:D,0),IF($E$1=2,INDEX('London & Districts'!R:R,MATCH('Regions Summary'!K63,'London & Districts'!D:D,0)))))

    K63 also has the following formula is =IF($E$1=1,LARGE(South!D:D,'Regions Summary'!A61),IF($E$1=2,LARGE('London & Districts'!D:D,'Regions Summary'!A61))) Which works.

    So what I want to happen depending on the number in E1 I will have the largest number of that data appear in K63, and then in my next cell I want it to index match again depending what number appears in E1.

    Is this possible?

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

    Re: Nested If Statement in INDEX MATCH

    I think you just have a misplaced )

    =IF($E$1=1,INDEX(South!R:R,MATCH('Regions Summary'!K63,South!D:D,0),IF($E$1=2,INDEX('London & Districts'!R:R,MATCH('Regions Summary'!K63,'London & Districts'!D:D,0)))))
    should be
    =IF($E$1=1,INDEX(South!R:R,MATCH('Regions Summary'!K63,South!D:D,0)),IF($E$1=2,INDEX('London & Districts'!R:R,MATCH('Regions Summary'!K63,'London & Districts'!D:D,0))))

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Nested If Statement in INDEX MATCH

    It's possible but it's still not clear what you are trying to accomplish. I am going to take a guess that your parentheses are in the wrong place. Your IF is in the place where an optional third argument to INDEX would be expected but I don't think that's what you intend. Maybe you want this:

    =IF($E$1=1,INDEX(South!R:R,MATCH('Regions Summary'!K63,South!D:D,0)),IF($E$1=2,INDEX('London & Districts'!R:R,MATCH('Regions Summary'!K63,'London & Districts'!D:D,0))))
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Registered User
    Join Date
    03-17-2017
    Location
    London
    MS-Off Ver
    2013
    Posts
    4

    Re: Nested If Statement in INDEX MATCH

    Thank you both!!!!!

    It was just a misplaced )

    It was driving me crazy.

    Thank youuuuuuuuuuuuuuu

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Nested If Statement in INDEX MATCH

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

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

    Re: Nested If Statement in INDEX MATCH

    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. If nested index match
    By trinialaskan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-29-2016, 10:39 AM
  2. VLOOKUP or INDEX-MATCH + INDIRECT with Nested IF/OR Statement
    By djmyers in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2015, 10:40 PM
  3. [SOLVED] Nested IF Statement with INDEX and MATCH
    By utahguy9384 in forum Excel General
    Replies: 11
    Last Post: 05-23-2014, 03:41 PM
  4. [SOLVED] 3 Nested IF INDEX MATCH
    By JonesZoid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-09-2014, 10:56 AM
  5. [SOLVED] Combining Nested IF statement with Index/Match??
    By consulttk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-21-2012, 09:48 AM
  6. nested if statement with index and match within
    By darkowen in forum Excel General
    Replies: 3
    Last Post: 02-01-2012, 02:02 AM
  7. Nested Match(index()) vs. Match() array formula.
    By GeneralDisarray in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2011, 07:57 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