Pull data from a table based on multiple criteria - old formula trying to use AND

I have an old formula I got off of this forum that I'm trying to modify to look in two different columns.

{=IFERROR(INDEX('IP Scheme'!G:G, SMALL(IF(AND(T_IP_Scheme[Device Type]="Camera",T_IP_Scheme[Service Schedule]="Monthly"), ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")}

I added the (AND(T_IP_Scheme[Device Type]="Camera",T_IP_Scheme[Service Schedule]="Monthly") part just now and it will not return even though I think I'm using the AND statement properly. It is currently returning 0 with no errors.

If I drop it down to only searching T_IP_Scheme[Device Type]=Camera I get a fully populated list. However we are adding our service plan to the spreadsheet and I now need to sort them by how often we will be servicing said cameras.

Any help would be very appreciated.

Thank you,

Tim Weaver

Try multiplying the criteria against one another:


{=IFERROR(INDEX('IP Scheme'!G:G, SMALL(IF((T_IP_Scheme[Device Type]="Camera")*(T_IP_Scheme[Service Schedule]="Monthly"), ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")}

The AND function is boolean, meaning it only returns a single True or False.

Unfortunately, it doesn't retain the array of evaluations for every input. Multiplying the criteria against one another however will create a set of 1's and 0's which are interpreted by IF as Trues and Falses.

It's difficult to imagine how this would work without an attached example.

If you're just trying to pull values from both columns, you could concatenate the expressions.
{=IFERROR(INDEX((('IP Scheme'!G:G)), SMALL(IF(T_IP_Scheme[Service Schedule]="Monthly", ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")} & "," &
{=IFERROR(INDEX((('Door Schedule'!Q:Q)), SMALL(IF(T_IP_Scheme[Service Schedule]="Monthly", ROW(T_IP_Scheme[Service Schedule])), ROWS(A$2:A2))), "")}