Excel Formula to display value if number is within in a range

[mikkydoos]

Hi all,

 

Hopefilly there is an Excel guru here who can shed some light on this.

 

Been asked to do a sheet whereby if a value entered in a cell is within a specific range of numbers, another cell will display a pre-determined value.

 

It would be simple but ...... this is a the range example.... it goes up to 50 and most of the range values are the same except a few like row 3 below.

 

 

Columns are: Lower range, Upper range, Display Value.

 

 

E.g. (Row 2) If num is => 3 AND <=5 then display 80

 

 

0 2 0

3 5 80

6 6 82

7 7 83

8 8 84

9 9 85

 

 

 

Can anyone shed a good method of how this could be done ??

 

 

 

Cheers in advance

[bald_pig]

A vlookup would probably be the easiest way of doing this, otherwise you're nesting a lot of if statements...

[mikkydoos]

There are too many statements to nest which is part of the problem

[mikkydoos]

Can you give me an example please @bald_pig Not sure how that would work with a range

[MrMat]

Yeaah a Vlookup with a True switch would make the most sense. The True means it's a 'closest match' which basically means you can use a table as a lookup and if the range is 0 - 5, anything within that range (i.e. 0,1,2,3,4,5) will return the answer as required.

 

See link here for details (the example shows a single column lookup, but the principle is the same ): https://www.excelcampus.com/functions/vlookup-closest-match-true/

 

and here's a super quick example i set up using your table

 

Edited October 24, 2019 by MrMat

[mikkydoos]

@MrMat

 

You're a bloomin' genius. Thank you very much. That works an absolute treat

[bald_pig]

You should be able to do away with the middle row to tidy it up a little more.

[mikkydoos]

You should be able to do away with the middle row to tidy it up a little more.

 

That makes sense.

 

 

Cheers @bald_pig