Referencing above/below based on two column values

[y3tter]

I'm trying to reference Above/Below by looking at column range AF48:AF61 and looking for the word "Red". The second reference is "Base" in column range AH48:AH61. If Red is in a Row above Base, I want to return the value "Above", if Red is in a row equal or below Base, I want to return "Below". Below are three examples. Is there an easy way to accomplish this?

1. Return "Above"
2. Return "Below"
3. Return "Below"

Red
		Base

<tbody>
</tbody>

Red		Base

<tbody>
</tbody>

		Base
Red

<tbody>
</tbody>

 

[admiral100]

Hi,

Here are 2 options in case there is red above & below :

AI48 =IF(AH48="Base",LOOKUP("Red",AF47:AF49,{"Above","Below","Below"}),"")



AJ48 =IFERROR(LOOKUP(IF(AH48="Base",SUMPRODUCT(--("Red"=AF47:AF49),ROW($A$2:$A$4)),""),ROW($A$2:$A$9),{"Above","Below","Below","Above & Below","Above & Below","Below","","Above & Below"}),"")

	AF	AG	AH	AI	AJ
48	Red
49			Base	Above	Above
50
51	Red		Base	Below	Below
52			Base	Below	Above & Below
53	Red
54			Base	Above	Above
55
56	Red
57			Base	Above	Above
58
59	Red
60	Red		Base	Below	Above & Below
61	Red

<colgroup><col span="5"><col></colgroup><tbody>
</tbody>

 

[y3tter]

Thank you admiral100!!

 

[admiral100]

You are welcome