In a tab called WEEKEND I have the following:
cell Q2: LAA
cell R2: 38845-LAA
cell Q4: ATL
cell R4: 38845-ATL

In a tab called LAA:
cell S2: 38810-LAA
cell S3: 38811-LAA
cell S4: 38812-LAA
etc. all the way down to S163

Another tab call ATL has the same type of data as the LAA tab.

I would like a formula in cell S2 in the WEEKEND tab to do the following:
Find the value of cell R2 in column S of the LAA tab and enter Yes if the
corresponding cell in column B in the LAA tab says AWAY. I would then like
to be able to copy this formula into cell S4 to do the same thing but with
the ATL tab.

Let me know if you don't understand what I am asking and I will try and
explain. Thanks in advance.