Forum Discussion
Solved
trouble with array, IFERROR and INDEX formula
Hi,
I am having trouble trying to get the rows that meet two criteria returned from an array.
the formula I am using is:
=IFERROR(INDEX(Service[#All], SMALL(IF((Service[State]=C1)*(Service[Age group]=C2), ROW(Service)-MIN(ROW(Service))+1), ROW(INDIRECT("1:"&COUNTIFS(Service[State],C1,Service[Age Group],C2))))), "")
I have tried entering it enclosed {}
Any assistance would be appreciated
MJenner Since you tag your question with Office365, why not use FILTER in stead? Would this do?
=FILTER(Service,(Service[State]=C1)*(Service[Age group]=C2))This will spill all rows from the Service table where State = the value in C1 AND Age group equals the value in C2.
MJenner Since you tag your question with Office365, why not use FILTER in stead? Would this do?
=FILTER(Service,(Service[State]=C1)*(Service[Age group]=C2))This will spill all rows from the Service table where State = the value in C1 AND Age group equals the value in C2.
Thanks for your quick response Riny, that did the trick, I did have to create additional named ranges for State and Age group
=FILTER(Service,(Service_state=c1) * (Service_age=C20),"N/A")Cheers
