=IF() function and =VLOOKUP function for Tolerances

New Contributor

Hello all,

 

I was wondering if someone could help me with a quick function query.

I currently want the following equation;

if something is below 16mm, it will produce "275",

if something if between 16mm and 40mm, it will produce "265", 

and if something is between 40mm and 60mm, it will produce "255".

 

This usually works but using multiple =IF() functions, however, when it is picking up a value from a =VLOOKUP cell, it will only pick up the IF FALSE section of the command. 

 

The current formula I am using is 

=IF(F182<16,"275",IF(40>F182>16,"265",IF(60>F182>40,"255",FALSE))) and at the moment, it is producing "265" even though the cell number it is picking up is 14mm. 

 

If I use this formula with the same number (14mm) but manually typed into a cell, it will work.

 

I appreciate any feedback. Thank you.

 

Charlie

5 Replies

@Charlie_Rees 

The formula should be

 

=IF(F182<16,"275",IF(F182<40,"265",IF(F182<60,"255")))

 

But do you really want to return a text value? If it should be a number, change the formula to

 

=IF(F182<16,275,IF(F182<40,265,IF(F182<60,255)))

Hello Hans,

Thank you for the reply, unfortunately the return value is now printing "FALSE".
I believe it has something to do with the =VLOOKUP function it is picking the number up from. As I said, if I try it was manually entering a number it works, but it needs to pick up something from a schedule.

Thanks.

@Charlie_Rees 

Perhaps the VLOOKUP function returns a text value such as "57" instead of the number 57?

Hi Hans,

Yes that seems to be the problem, is there a way to convert a large number of text values into numeric values? I have changed the specific =VLOOKUP value and the command has started working.

Thanks.

@Charlie_Rees 

It depends on the formulas. Perhaps you can replace " with nothing in the cells with the formulas, if they don't contain other quotes...