CODEWITHSUNDEEP
excellookup
n8-da-gr8
n8-da-gr8

Reputation: 551

Find first and second occurrence with condition

I have the table below...

Key   Result
1       T
2       G
2       T
3       T
3       T
4       G
4       T
4       T
5       G
5       T
5       T

I need to perform a lookup which will locate the Key and check whether that Key has T or G for the Result, and give 1 if it does and 0 if not.

So for the above table, my two formulas should return the following...

Key   T   G
1     1   0
2     1   1
3     1   0
4     1   1
5     1   1

Obviously VLOOKUP won't work because it only finds the first occurrence, so I tried using INDEX-MATCH

=INDEX($B:$B,MATCH($A2,$A:$A,0),1)

The above formula returns the Result for each Key, but how would I modify it to return 1 if the result is T and 0 otherwise?

EDIT: SOLUTION

=IF(COUNTIFS(A:A,E2,B:B,F1)>0,"1","0")

enter image description here

Upvotes: 0

Views: 1204

Answers (3)

CharlesPL
CharlesPL

Reputation: 331

Have you ever heard of array (CTRL + Enter) formulas ?

Assuming you only want to know if there is a G result for one key, this is what I would suggest you:

  1. Do a multiplication of the comparaison between your key and your value.

    =($A$2:$A$12=KEY)*($B$2:$B$12=RESULT) (Where KEY and RESULT are the cells for your actual values (1,2,3... for the KEY, T or G for the RESULT) then press CTRL+ENTER. If you use the Evaluate Formula button, you'll understand how this is working quite easily.

  2. If you simply do a MAX on this array, then you'll end up with a 1 if your request (i.e. if you have both your KEY and your RESULT in your "table"), otherwise, you'll have a 0.

Using this approach, but changing the MAX by a SUM will give you the number of occurrences where your criteria are matched.

Remember to always press CTRL+ENTER when you finish editing your array formula!

Final formula =MAX(($A$2:$A$12=$A19)*($B$2:$B$12=B$18))

Upvotes: 0

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

There are many ways to achieve this, here's an example for two of them:

Assumes lookup table is in Sheet2!A:C

String Concatenation in MATCH()

=--ISNUMBER(MATCH($A2&"T",$A:$A&$B:$B,0))

or =--ISNUMBER(MATCH($A2&B$1,Sheet1!$A:$A&Sheet1!$B:$B,0))

Using COUNTIF()

=--(COUNTIFS($A:$A,$A2,$B:$B,"T")>0)

or =--(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1)>0)

You can use IF([],1,0) instead of the --

Upvotes: 1

pnuts
pnuts

Reputation: 59475

Assuming Key is in A1, please construct the headers for your output (say with Key in D1) then in E2:

=1*(COUNTIFS($A:$A,$D2,$B:$B,E$1)>0)

copied across and down to F6.

Upvotes: 0

Related Questions