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

1. ## 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.

Thanks,

Nic.

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

Can you attach a sample file Nic?

3. ## 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. ## 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.

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

Would a SUM SEARCH work for this?

##### Users Browsing this Thread

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

#### 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