IF AND EXACT formula with two conditions

Occasional Contributor

Can someone help create above formula? I need to input a formula in Column J. It needs to look at both Columns I and O. If Column O has the words "Opt-Out" within it, then this is the secondary criteria to consider. Shift rates are shown on second worksheet tab (picture far below).


Screen Shot 2022-07-05 at 9.53.59 AM.png

Shift Diff tab:

Screen Shot 2022-07-05 at 9.55.46 AM.png



2 Replies
best response confirmed by Sergei Baklan (MVP)

@Teresa Smagacz 

In J2:

=INDEX('Shift Diffs'!$C$2:$C$7, I2+3*ISNUMBER(FIND("Opt-Out", O2)))

Fill down.

Genius. TY!