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

excel

Formulas and Functions

office 365

Riny_van_Eekelen
Sep 13, 2023
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.

Riny_van_Eekelen

Platinum Contributor

Sep 13, 2023

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

Copper Contributor

Sep 13, 2023

Riny_van_Eekelen

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

Forum Discussion

trouble with array, IFERROR and INDEX formula

Resources