Checking for criteria in whole column

Question

Hello, I am trying to get a formula that will lookup a criteria in one column and then return value in another column if that too matches a second criteria. How do I do this? Thanks

tp700 · Answer

TP700I have attached an example version of this is my file to visualise what I need it to do

peterbartholomew1 · Answer

A concrete example with the expected result (hand calculated) would be useful.

tp700 · Answer

Hi Peter, I can't send my actual working file as it contains sensitive data, so I've put more information into my example file to show what I mean. Thanks

peterbartholomew1 · Answer

TP700&nbsp;A formula you might care to try is= COUNTIFS(uniqueCode, uniqueCode, type, "Type1")where, hopefully, the defined names are obvious.&nbsp; With 365 the formula should spill to the entire column.

sergeibaklan · Answer

TP700&nbsp;
Depends on in which form you'd like to have the result. As variant

with
=LET(
    types, TOROW(SORT(UNIQUE(type))),
    codes, SORT(UNIQUE(code)),
    VSTACK(
        HSTACK("",types),
        HSTACK(codes,
            MMULT(--(TRANSPOSE(TOROW(codes)=code)),
                  --(type=types))
        )
    )
)

Forum Discussion

Checking for criteria in whole column

9 Replies