XLOOKUP & LOOKUP formula

[buh149]

Hi all,
I have a formula which works, but I feel could be made more simple - see cell H13 (image attached - sorry, the xl2bb addin doesn't work for me!).

Named ranges:
MIN = cells C5:C8
MAX = cells D5:D8
AD = cells E5:E8
IN: cells F5:F8
BR: cells G5:G8

I feel that I could remove the "IF" part of these formulas and surely replace with an XLOOKUP or something else? E.g. in the case of the formula in cell H13, where it tells you which column to take the % fee from based on the value in column C ("AD", "IN", or "BR"). The LOOKUP part of the formula works fine but I just think there must be a better way to say "if the value in column C is X, then take it from column A", etc, at the same time using the LOOKUP formula to take from the correct row based on column D. Maybe I've been looking at it too long and am missing something very obvious.

Figures have been changed and made up to keep things simple and anonymous.

Thanks to anyone that can help!

 

[Fluff]

Myabe

=LOOKUP(2,1/((D13>=MIN)*(D13<=MAX)),INDEX(E5:G8,,MATCH(C13,E4:G4,0)))

 

[buh149]

Thank you!! This works! Much easier to read. I just amended one small error which was the D13>=MIN which was changed to D13>MIN, but otherwise this is great. Much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.