Solved: Vlookup with an Index & Match formula

[JimS]

I need to grab the value from a table based on how another cell aligns/matches to its values.

The attachment shows what I’m trying to do.

Thanks for any help...

JimS

[BrianMH]

Do you need to be able to add values to the color codes or is there always just going to be the 4? Are you happy with a UDF?

[nepotist]

Is the purpose of this to finally color the cells or does color code actually important?if it is just the color that is important then did you try conditional formatting?

[JimS]

No, I need to value from the Table, it's not for Conditional Formatting.

[shrivallabha]

I've tried this with Named Range as the normal VLOOKUP and MATCH don't work if the LOOKUP ARRAY is on the left side. The table 'A7:B18' is now a named Range. The formula is built only for this case, it may become unwieldy for more cases.

I am attaching the workbook and there's a bit of doubt as Cases seem to change as Lower Limit<Value=>Upper Limit. I've marked the case 2 doubt in the worksheet.

[JimS]

Thanks, that's one large formula.

Thanks again...

JimS