Need to find a row in a table with a stocker ticker and text I specify

[Ridgebacks]

I need a formula that looks at the value in cell A1 (which will be a stock ticker), and then references a table with stock tickers in column B and text in column C.
The table goes from B1:C5
It needs to find a row in column B1:B5 with the same ticker as A1 and also a text value I specify in column C1:C5
Ticker Text

B C
ABC Red
ABC Blue
ABC Green
ABC Yellow
ABC Orange

Cell A1 has the Ticker ABC
The formula will be in cell A2
The formula will look for a row with ABC and the text “Yellow”
If it finds a match, it puts the word “Yes” in A2, if not it puts the word “No”.
Is there an easy way to do this?

Thanks.

[ChemistB]

I would put Yellow into a cell rather than hardcoding it into the function but you can use an array function. It must be entered with CNTRL SHFT ENTER instead of ENTER (you'll see brackets {} around it if done properly)

=IF(ISNUMBER(MATCH(A1&"Yellow",$B$1:$B$5&$C$1:$C$5,0)), "Y", "N")

Does that work for you?

[alansidman]

Does this work for you?

=IF(INDEX(Sheet2!B1:C5,MATCH("yellow",Sheet2!C1:C5,0),2)="Yellow","Yes","No")

Edit: I concur with Chemist about not hardcoding the term "yellow"