+ Reply to Thread
Results 1 to 5 of 5

Return the value of a cell/multiple criteria using nested INDEX, MATCH, IF array formula

  1. #1
    Registered User
    Join Date
    01-15-2014
    Location
    Glasgow
    MS-Off Ver
    Excel 2007
    Posts
    13

    Return the value of a cell/multiple criteria using nested INDEX, MATCH, IF array formula

    Hi All,

    I am trying to return the value of a range of cells if it meets multiple criteria.

    I have got this working on 2 criteria but need to add in a third, and can not get it to work.

    {=IFERROR(INDEX('CR Data Entry'!$H$2:$H$1000,MATCH("*Barnwood*", IF('CR Data Entry'!$AL$2:$AL$1000=$D22, 'CR Data Entry'!$P$2:$P$1000,""),0)),"")}

    So this finds the value in range 'CR Data Entry'!$H$2:$H$1000 if it meets Barnwood and week commencing($D22).

    I now need it to also meet criteria of ">=270" in range 'Change Impact Calculator'!$R$2:$R$1000.

    Can anyone please help with this.

    Thanks,

    Nic.

  2. #2
    Forum Contributor
    Join Date
    01-17-2012
    Location
    Ulsan, South Korea
    MS-Off Ver
    Excel 2013
    Posts
    215

    Re: Return the value of a cell/multiple criteria using nested INDEX, MATCH, IF array formu

    Can you attach a sample file Nic?

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,730

    Re: Return the value of a cell/multiple criteria using nested INDEX, MATCH, IF array formu

    Maybe:
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    01-15-2014
    Location
    Glasgow
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Return the value of a cell/multiple criteria using nested INDEX, MATCH, IF array formu

    Attachment 307450

    Hi,

    I have attached the file i am working on. I actually got the formula to work on another file with multiple criteria but for some reason on this one it will not bring anything back.

    Cell F82 of the "Data Sheet TOTP Q1" should be bringing back 30. When i step into the formula it seems to be an issue with the wildcards around E82 (Savings Sales) I can not figure this out at all.

    In theory this should work:

    {=IFERROR(INDEX('Data Input'!$AM$2:$AM$1000,MATCH("*"&$B82&"*",IF('Data Input'!$AL$2:$AL$1000=$D82,IF('Data Input'!$R$2:$R$1000="*"&$E82&"*",'Data Input'!$P$2:$P$1000, "0")),0)),"0")}

    I have no clue why it isn't.

    Is there another way i can do this?

    Thanks,

    Nic.
    Last edited by Nic31; 04-01-2014 at 09:42 AM.

  5. #5
    Registered User
    Join Date
    01-15-2014
    Location
    Glasgow
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Return the value of a cell/multiple criteria using nested INDEX, MATCH, IF array formu

    Would a SUM SEARCH work for this?
    Last edited by Nic31; 03-27-2014 at 01:12 PM.

+ 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. LOOKUP with Multiple Criteria (ARRAY INDEX and MATCH)
    By snowktt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-14-2014, 02:24 AM
  2. I want to exclude blank cell in index match formula with multiple criteria
    By NOOR8225 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-16-2013, 06:51 AM
  3. Replies: 6
    Last Post: 04-12-2013, 05:50 AM
  4. 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
  5. Replies: 3
    Last Post: 08-17-2010, 02:54 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