Find Column Value from Lookups

[RazzPrince]

Many time user....first time poster

I am looking to return a column value from a single row based on two variable (one in rows and one in columns). Hopefully it makes more sense in the table!
I have 'over engineered' a solution but hoping for something more streamlined to save workbook bulk also looking for a non VBA solution.

Thanks if you can help!!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Ref A[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ref B[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ref C[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ref D[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ref E[/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Required Value from this row[/TD]
[TD]Apr18[/TD]
[TD]May18[/TD]
[TD]Jun18[/TD]
[TD]Jul18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So from this data I need to return the Month from the Ref in Column A and then the number from Column B to E.

So if my variable were Ref B and 3, my formula would return Jun18.
If variable Ref D and 7, this would return Jul18.

My current formula works for Ref B and 3 by doing If("Ref B"=A1,Hlookup(3,B1:E1,6),if"Ref B"=A2,Hlookup(3,B1:E1,5),ETC)).

Scratching me head on this one so would appreciate any help/ suggestions! Hopefully I am missing the obvious.


Thanks,

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Ref A[/td][td]

1​

[/td][td]

5​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Ref B[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Ref C[/td][td]

1​

[/td][td]

3​

[/td][td]

4​

[/td][td]

4​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Ref D[/td][td]

1​

[/td][td]

2​

[/td][td]

2​

[/td][td]

7​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Ref E[/td][td]

1​

[/td][td]

7​

[/td][td]

7​

[/td][td]

7​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Required Value from this row[/td][td]

18-Apr​

[/td][td]

18-May​

[/td][td]

18-Jun​

[/td][td]

18-Jul​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Condition1[/td][td]Ref A[/td][td][/td][td]Formula:[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Condition2[/td][td]

1​

[/td][td][/td][td]

18-Apr​

[/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet18[/td][/tr][/table]

Formula in cell D9:
=INDEX($B$6:$E$6,MATCH($B$9,INDEX($B$1:$E$5,MATCH($B$8,$A$1:$A$5,0),0),0))

 

[RazzPrince]

Thanks a million for this! It makes sense now its 'spelt' out!

One more question if you can help...... If condition 2 was the number 4 (ie number less than 5), can the formula be updated to still pull back Apr-18 (if the condition is not an exact match)?