how to check if a cell value matches both criteria from the same range

[olgkik]

Hi everyone!

I was wondering if someone could help me figure out the formula for the following.
I am trying to find all cases, where a currency pair from the column B (see attached file) matches both 'buy' and 'sell' parametres from the column A. If this criteria is met, the Result (column C) is 'YES', otherwise 'NO'.

Thanks in advance.

[bebo021999]

Normally, buy-usdjpy and sell-usdjpy makes pair.

But, is it the case of buy-usdgbp and buy-gbpusd makes pair?

[JohnTopley]

Try

=IF(A2="buy",IF(AND(A3="sell",B2=B3),"yes","no"),IF(A2="Sell",IF(AND(A1="Buy",B1=B2),"yes",IF(AND(A3="Buy",B2=B3),"yes","no"))))

[bebo021999]

try array formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Drag down