Find data using three variables, all of which may repeat.

[Guest]

The worksheet lists card numbers in one column and text codes, package
labels, and status in other columns. Some card numbers repeat due to
multiple
codes and package labels but remain in ascending order. The text columns
are in random order and some also repeat for other card numbers, but there is
no
duplication of the same three values.

The task is finding a text code using an active card number and a package
label. See
simplified worksheet layout . . .

Cards Package Code Status

1145 BC02A BCAA Active
1250 BC07A BCAS Inactive
1250 BC07A BCAR Active
1250 BC07A BCDR Inactive
1300 BC01X BCAA Active
1350 BC07A BCBY Active
1375 BC07F BCBQ Inactive
1375 BC07F BCBR Inactive
1375 BC07F BCBS Inactive

1st Question: What formula can look up the "Active" card 1250 in package
BC07A and provide the code BCAR?

2nd Question: What formula can look up card 1375 in package BC07F and seeing
no active cards in all three listings for that card number and package,
insert the word "Inactive".

 

[Guest]

tr
=if(iserror(sumproduct(--(Statusrange="Active"),--(cardsrange=1250),--(packagerange="BC07A"),coderange)),"inactive",sumproduct(--(Statusrange="Active"),--(cardsrange=1250),--(packagerange="BC07A"),coderange))

the --( changes the logical true false to a numeric 1-0
the arrays in each section of sumproduct must be the same size but cannot be
the shorthand reference for entire rows or columns (A:A wont work)

you can, of course, reference a cell in the argument instead of using the
value .