IF and VLOOKUPS together

[donnarowbo]

Hi

Please can someone help me with the following:

I'm developing an excel based calculator that will return different results depending on how an entry compares to a vlookup result.

I.e:
If the value in cell E13 is less than the vlookup value returned, minus 5%, return "RED"
If the value in cell E13 is less than the vlookup value returned, minus 1%, return "AMBER"
If the value in cell E13 is greater than the vlookup value returned, return "RED"

For the life of me I can't get it to work.

Thanks in advance

Donna

 

[donnarowbo]

Sorry.... amended


I.e:
If the value in cell E13 is less than the vlookup value returned, minus 5%, return "RED"
If the value in cell E13 is less than the vlookup value returned, minus 1%, return "AMBER"
If the value in cell E13 is greater than the vlookup value returned, return "GREEN"

 

[Jonmo1]

I assumed you wanted GREEN if E13 was Equal to or greater than vlookup-1%

Try
=LOOKUP(E13,VLOOKUP(.....)*{0,0.95,0.99,1.01},{"RED","AMBER","GREEN","RED"})

 

[Jonmo1]

If the value in cell E13 is less than the vlookup value returned, minus 5%, return "RED"
If the value in cell E13 is less than the vlookup value returned, minus 1%, return "AMBER"
If the value in cell E13 is greater than the vlookup value returned, return "GREEN"

You're still missing one range...
BETWEEN (vlookup value returned, minus 1%) AND (Vlookup Value Exactly.)

 

[63falcondude]

Sorry.... amended


I.e:
If the value in cell E13 is less than the vlookup value returned, minus 5%, return "RED"
If the value in cell E13 is less than the vlookup value returned, minus 1%, return "AMBER"
If the value in cell E13 is greater than the vlookup value returned, return "GREEN"

Try something along the lines of

=IF(E13<(vlookup fomula - (vlookup formula*.05)),"RED",IF(E13<(vlookup formula - (vlookup formula*.01)),"AMBER",IF(E13>vlookup formula,"GREEN","")))

Edit: Jonmo's is better than mine, I don't even know if this would work...

 

[Jonmo1]

Edit: Jonmo's is better than mine, I don't even know if this would work...

I'm sure it would have worked fine (to the OP's description anyway)

FYI
vlookup fomula - (vlookup formula*.05)
Is the same as
Vlookup formula * 0.95

 

[63falcondude]

I'm sure it would have worked fine (to the OP's description anyway)

FYI
vlookup fomula - (vlookup formula*.05)
Is the same as
Vlookup formula * 0.95

Good catch.

 

[donnarowbo]

You absolute legends! Thank you so much! All done and sorted!

 

[Jonmo1]

You're welcome.