index\match 4th critera not working

[KDS14589]

Hi

I'm having problems adding a forth criteria to this formula =IFERROR(INDEX(statement_name,AGGREGATE(15,6,(ROW(statement_name)-ROW(Statement!$AA$10)+1)/(statement_Bal_this_month>=$BC$16)/(statement_Bal_this_monthl<$BC$17)/(statement_min_due>0)/(statement_name,Avoid_name),ROWS(Statement!BN$10:BN10))),"")

It's the statement_name,Avoid_name part

I've used the same formula minus the troubling part with success in other parts but if I add that forth part it stops.

That part refers to a name that I want to include as the result. If I don't have that part, I'll have 11 names but when I add that part, I get no name when I want just the one name it refers to.

If I get this working, I want to modify my other formulas to show the names minus the referred name because I just now notice this problem.

=IFERROR(INDEX(statement_name,AGGREGATE(15,6,(ROW(statement_name)-ROW(Statement!$AA$10)+1)/(statement_Bal_this_month>=$BC$16)/(statement_Bal_this_monthl<$BC$17)/(statement_min_due>0)/(statement_name,Avoid_name),ROWS(Statement!BN$10:BN10))),"")

 

[Marcelo Branco]

Not sure what you are trying to do...
A **guess**....
Try this on the 4th criterion
(statement_name<>Avoid_name)

M.

 

[KDS14589]

I tried that before but with no luck, thanks for the suggest though.
I'm trying to get just one listing without rewriting the complete formula just for this section. the other sections will have this 'Avoid_Name" as a disclaimer criteria

 

[KDS14589]

I tried that before but with no luck, thanks for the suggest though.

I'm trying to get just one listing without rewriting the complete formula just for this section. the other sections will have this 'Avoid_Name" as a disclaimer criteria


More Info:

Without that criteria I'm getting a list with that name included.

In the other sections I get a listing that is restricted to the values in BC16 and BC17 and a 'min_due' more than 0 (those work)

In 'Avoid_Name' is a name that I want the other sections lists to ignore

 

[Marcelo Branco]

It worked for me with a small sample of data (see below)

Observe at the example
- the named ranges
- John meets the conditions >=Min ($E$2) and <=Max ($F$2)
- John is in the Avoid_name range and therefore, despite meeting the above conditions, he does not appear in the list
- the others that meet the Min and Max conditions appear in the list

Pasta1
	A	B	C	D	E	F	G	H
1	statement_name	statement_Bal_this_month		Avoid_name	Min	Max		List
2	Albert	10		John	30	40		Diana
3	Diana	35						Mike
4	John	38						Richard
5	Mike	32
6	Richard	32
Plan1

Cell Formulas
Range	Formula
H2:H5	H2	=IFERROR(INDEX(statement_name,AGGREGATE(15,6,(ROW(statement_name)-ROW(INDEX(statement_name,1))+1)/((statement_Bal_this_month>=$E$2)*(statement_Bal_this_month<=$F$2)*(statement_name<>Avoid_name)),ROWS(H$2:H2))),"")

Named Ranges
Name	Refers To	Cells
Avoid_name	=Plan1!$D$2	H2:H5
statement_Bal_this_month	=Plan1!$B$2:$B$6	H2:H5
statement_name	=Plan1!$A$2:$A$6	H2:H5

Hope it helps

M.

 

[KDS14589]

It worked for me with a small sample of data (see below)

Observe at the example
- the named ranges
- John meets the conditions >=Min ($E$2) and <=Max ($F$2)
- John is in the Avoid_name range and therefore, despite meeting the above conditions, he does not appear in the list
- the others that meet the Min and Max conditions appear in the list

Pasta1
	A	B	C	D	E	F	G	H
1	statement_name	statement_Bal_this_month		Avoid_name	Min	Max		List
2	Albert	10		John	30	40		Diana
3	Diana	35						Mike
4	John	38						Richard
5	Mike	32
6	Richard	32
Plan1

Cell Formulas
Range	Formula
H2:H5	H2	=IFERROR(INDEX(statement_name,AGGREGATE(15,6,(ROW(statement_name)-ROW(INDEX(statement_name,1))+1)/((statement_Bal_this_month>=$E$2)*(statement_Bal_this_month<=$F$2)*(statement_name<>Avoid_name)),ROWS(H$2:H2))),"")

Named Ranges
Name	Refers To	Cells
Avoid_name	=Plan1!$D$2	H2:H5
statement_Bal_this_month	=Plan1!$B$2:$B$6	H2:H5
statement_name	=Plan1!$A$2:$A$6	H2:H5

Hope it helps

M.

It works

I also found an extra letter after one of the name_range I used (my shaky hands due to my disability) in that section. I'm going to double check the other sections formulas.

THANKS for the help

 

[Marcelo Branco]

You're welcome. Glad to help.

M.