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"

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Base[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[TD]Base[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Base[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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"}),"")

[TABLE="width: 454"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[TD]AI[/TD]
[TD]AJ[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD][/TD]
[TD][/TD]
[TD]Base[/TD]
[TD]Above[/TD]
[TD]Above[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]51[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]Base[/TD]
[TD]Below[/TD]
[TD]Below[/TD]
[/TR]
[TR]
[TD]52[/TD]
[TD][/TD]
[TD][/TD]
[TD]Base[/TD]
[TD]Below[/TD]
[TD]Above & Below[/TD]
[/TR]
[TR]
[TD]53[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD][/TD]
[TD][/TD]
[TD]Base[/TD]
[TD]Above[/TD]
[TD]Above[/TD]
[/TR]
[TR]
[TD]55[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]56[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]57[/TD]
[TD][/TD]
[TD][/TD]
[TD]Base[/TD]
[TD]Above[/TD]
[TD]Above[/TD]
[/TR]
[TR]
[TD]58[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]59[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD]Base[/TD]
[TD]Below[/TD]
[TD]Above & Below[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[y3tter]

Thank you admiral100!!

 

[admiral100]

You are welcome