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

**Nic31** · 03-25-2014, 10:53 AM

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.

**jeversf** · 03-26-2014, 12:12 PM

Can you attach a sample file Nic?

**bebo021999** · 03-26-2014, 12:23 PM

Maybe:

Please Login or Register  to view this content.

**Nic31** · 03-27-2014, 01:00 PM

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.

**Nic31** · 03-27-2014, 01:09 PM

Would a SUM SEARCH work for this?

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

LOOKUP with Multiple Criteria (ARRAY INDEX and MATCH)

I want to exclude blank cell in index match formula with multiple criteria

[SOLVED] Index Match array formula need to pull names whose adjacent cell matches given criteria

Nested Match(index()) vs. Match() array formula.

Vlookup or Index / Match for multiple criteria and return a date value from cell

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions