matching 3 criterias to return a sum

[stephen4321]

Hi,

I'm really struggling to match 3 criterias and return the cell data in B4:G6. It seems to work when I'm not search more than one cell but as soon as I want to search a criteria over several cells i.e. B2:G2=A3 it returns N/A but if the serach is simply B2=A3 then it returns?

=INDEX(Sheet1!B4:G6,MATCH(1,IF(Sheet1!B2:G2=A3,IF(Sheet1!B3:G3=A4,IF(Sheet1!A4:A6=A6,1))),0))

I've arrayed the formula but still struggling, hopefully someone can help??

 

[jadhava15]

Hi,

Kindly elaborate a bit more with sample data and desired output.

Regards,
Amitkumar J.

 

[stephen4321]

Hi,

I have a sample excel sheet but can't upload it, basically I have 3 criteria:

=INDEX(Sheet1!B4:G6,MATCH(1,IF(Sheet1!B2:G2=A3,IF(Sheet1!B3:G3=A4,IF(Sheet1!A4:A6=A6,1))),0))

A3 - Location
A4 - Total Cost
A6 - Manager


I am trying to return the Manager cost from a data table on Sheet 1 B4:G6.

Hope that helps.

 

[jadhava15]

Can use below formula after some modification.

=INDEX(Sheet1!B4:G6,MATCH(1,(Sheet1!B2:G2=A3)*(Sheet1!B3:G3=A4)*(Sheet1!A4:A6=A6),1),2)

Regards,
Amitkumar J.

 

[stephen4321]

Thanks for your help but it's still returning #N/A

 

[Aladin Akyurek]

Hi,

I have a sample excel sheet but can't upload it, basically I have 3 criteria:

=INDEX(Sheet1!B4:G6,MATCH(1,IF(Sheet1!B2:G2=A3,IF(Sheet1!B3:G3=A4,IF(Sheet1!A4:A6=A6,1))),0))

A3 - Location
A4 - Total Cost
A6 - Manager


I am trying to return the Manager cost from a data table on Sheet 1 B4:G6.

Hope that helps.

Are we retrieving a fgure or summing?

In which ranges do we have locations, mangers, and total cost values?