Comparing percentage values against constants, less than, greater than, and ranges

[bevtyler]

I have two small tables where I need to compare percentages in columns E, F, G to the values/ranges in A, B, C.
Is there an easier way to do this than the long formula I have started to build?
(file attached)



The yellow formula in cell [I2] is below, which has the following checks:
- IF: compare exact amounts
- IF: compare amount within +-2
- IF: the cell contains a "<" less than sign,
- then IF compare it to "only the numbers within the cell of the first table"; [ VALUE ((TEXTJOIN...)))) ] will return 18 for cell [A3]
- else IF: the cell contains a ">" greater than sign, then compare it to "only the numbers within the cell of the first table" ...
- Otherwise, "not greater than"

=IF((E2*100) = (A2*100),"green",
IF(AND((E2*100) >= (A2*100)-2, (E2*100) <= (A2*100)+2),"yellow",
IF(ISNUMBER(SEARCH("<",A2)),
IF((E2*100) < VALUE((TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")))),
"green",
"red"),
IF(ISNUMBER(SEARCH(">",A2)),
IF((E2*100) > VALUE((TEXTJOIN("",TRUE,IFERROR((MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*1),"")))),
"green",
"red"),
"not greater than")
)))


I still need to add:
- Checking for a range (if there is a dash "-"), and determining how to compare the numbers

When I copy the yellow formula to the blue cells, then the first two IF statements break where the first table has a TEXT CHARACTER in it.
- I am multiplying the percentage by 100 to standardize the number set, since the second IF statement needs to add or subtract 2.
- As well, the [ VALUE ((TEXTJOIN...)))) ] returns a whole number rather than a percentage

Thanks for any help you can give!