Find identical item, look for true 2 columns over.

[bluerog]

I’m putting together a blackjack odds calculator. I could use some help with a formula. If I have an A,7 (ace low) it needs to return a 0 (or false) for a “Possible Hand” because further down the column A,7 (ace hi) has a true “End Hand Total”

I was going to use an IF and a VLOOKUP (looking lower on the column), but then I cannot sort the column. Is there a formula that will find the hand and check for true 2 columns over from identical hand.

There are 169 starting hand combinations. The attached worksheet only has 31 – for simplification.

Thank you much

[6StringJazzer]

If I have understood your description properly, you can use the following formula in G2 and copy it down. I also attached an updated workbook.

Note that I believe that G8 should also have a 0, based on your description.

This looks at the current cell and determines if any of the cells below it with the same card combination contains an "End Total Hand" value of 1. If so, it returns 0; if not it returns 1. I am not certain that you only want to look below the current cell, but that seems like what you're doing in the example.

=IF(SUMIF(C3:C$32,C2,F3:F$32)=0,1,0)

This assumes that your data ends at row 32, update to suit your specific situation (170?).

[bluerog]

Thank you much 6String. That should do the trick.