Checking for criteria in whole column

[TP700]

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

[#2]

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

[#3]

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

[#4]

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

[#5]

@TP700 

A formula you might care to try is

= COUNTIFS(uniqueCode, uniqueCode, type, "Type1")

where, hopefully, the defined names are obvious.  With 365 the formula should spill to the entire column.

[#6]

@TP700 

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

[#7]

@Sergei Baklan 

Agreed, there are better layouts.  To be told in triplicate that there are three occurrences would seem to be excessive.  Mind you, your skills are soon to be overtaken by

= PIVOTBY(uniqueCode, type, type, COUNTA, ,0, ,0)

[#8]

@Peter Bartholomew 

So far I don't use these two aggregation functions due to compatibly. Played a bit and keep for the future.

[#9]

@Sergei Baklan 

I see your point. 

Perhaps it depends on whether you are writing for the past, present or future!

- Most readers do not like change and will be most comfortable with the past

- The present is the most useful

- The future stretches the imagination 

The new functions provide amazing flexibility for pivot-table-like output 

Re: Is now the time to abandon the concept of relative referencing? - Microsoft Community Hub

but also can figure in the core calculation task.  For me, practice is going to be necessary!

[#10]

@Peter Bartholomew 

Major part of what I'm doing I share with other people. Major part of such people are on Current channel. On this resource I assume the same, if only it's not clear from the content that the person is on legacy Excel, actually or mentally. Or, in opposite, on Beta.

Thus the assumption is people are in present. Agree, that's great if someone tries to prepare them for the future.

As for pivoting I still prefer data model based PivotTables. Lot of flexibility, especially with complex models, easier to slice & dice, more flexible formatting. Main cons is Refresh.

For the relatively simple models new functions could be more preferrable. When they are in production.