Need one large nested formula 7 diffent results

[JET2011]

I'd like to make one formula to test for 7 different ranges of values in column A and assign a value of 1 - 7 in column B depending on the value in column A. For example if column A1 had a value of 235 B1 would get assigned a value of 4 because it is greater than 233 but less than or equal to 6,210.

[Raphaelp]

Don't be lazy! If you know how to use the IF function, it shouldn't be too difficult!

[FlameRetired]

JET2011,

Edited Re read.
Put the values in an array constant descending. Use an approximate MATCH for the choices.

[JET2011]

I've simplified the problem with whole numbers to make it easer for someone to hopefully to assist.

[MrShorty]

I will agree with Flame Retired -- rather than an IF() function, use a simple lookup function (MATCH() function specifically). Your values are sorted in descending order, so you will need to use -1 as the 3rd argument of the MATCH() function.
Match function help file: https://support.office.com/en-us/art...9-533f4a37673a
What part of using the MATCH() function do you get stuck on or don't understand?

[JET2011]

Never used it but I'll scout around for more examples. Most examples I found were way more complex. Will report back. - j

[MrShorty]

Scout around for examples. I have noticed that the vast majority of examples of Excel's lookup functions are for exact matches (3rd argument of MATCH() function is 0) and very few examples illustrate the behavior of the approximate match options. In those cases, use the examples to get a basic idea of the syntax of the MATCH() function. Once you understand the syntax, you will probably need to try it out with the approximate -1 option in your own spreadsheet so you can see how the approximate match option works.

If it helps, I created this tutorial that has an example (using VLOOKUP()) of this approximate match behavior: https://www.excelforum.com/tips-and-...p-example.html VLOOKUP() cannot work with descending data like the MATCH() function can, but it will hopefully help you see how it should work.

[Raphaelp]

Put this code in a module. Then in excel, enter formula =GreaterOrLess(A2*). *Click on a cell

Please Login or Register  to view this content.

[FlameRetired]

JET2011 I failed to mention what I meant by array constant. (I also left out other details which MrShorty covered.)

The references cited use some array constants. It doesn't appear they state that explicitly.

In the original data an array constant descending in the second argument of MATCH would look like this.

{308538,66807,6210,232,3.4,0.019,0}

Hope that helps.