Label cell if between values for list ISSUE!

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
Hello excel enthusiasts,

I'm sure there is a way to do it, but I can't figure out the simplest way short of a really long really nested if function.

I have a list (900 lines long) and then I have another list which corresponds to scores. Here is an example:

100 - A
90 - B
80 - C
70 - D
60 - E
50 - F
40 - G
30 - H
20 - I
10 - J
0 - K

In the real sheet the numbers are one column and then letters are the next column. I also have another list like this:

30
34
35
90
93
40
64
...

Basically i want to put in a formula into the cell next to the first value in the list immediately above so I can drag it down and have it put the corresponding value from the comparison chart (the first example above).

The only way I thought of was to do =if(and([CELL w/ first score]>[lower bound score- 90],[CELL w/ first score]<[upper bound score- 100],[Cell next to upper bound- A],if(and(... and then replace the bounds with the next set so the next upper bound would be the previous lower bound...

but because the list with the bounds is long as well 50 cells, that would be a really long really nested if statement.

There MUST be a more efficient way of doing this.

Thanks!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I'm not sure I unsderstand fully, but let's say your 2 column list is in the range A1:B11 and that your second list is in column C starting at C1. In D1 enter:

=INDEX($B$1:$B$11,MATCH(C1,$A$1:$A$11,-1))

and copy down.

The formula will return the letter from the 2 column list that corresponds with the number in column C, allowing for non-exact matches.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,352
As long as you dont change the values in the first table, seeing as all your values are in steps of 10...

=CHAR(11-A1/10+64)

will work

where column A is your second list, 30, 34 etc


Update: Mm this doesnt work... :(
 
Last edited:

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
1) First, reverse your table so the values are ascending, as shown below.
2) Here are THREE different formulas that all do the thing you're after:

Excel Workbook
ABCDE
1TierValueScoreValue
20K30H
310J34H
420I35H
530H90B
640G93B
750F40G
860E64E
970D
1080C
1190B
12100A
Sheet1



The trick here is to make sure the column B values is the result you want returned when the match rounds down in column A.
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,352

ADVERTISEMENT

...but this does!

=CHAR(11-INT(A1/10)+64)
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Using my chart above, this version of the INDEX/MATCH formula will cause the values to roundUP if they are not an exact match:

Excel Workbook
ABCDE
1ValueScoreValue
20K30H
310J34G
420I35G
530H90B
640G93A
750F40G
860E64D
970D
1080C
1190B
12100A
Sheet1
 

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
Thanks!

I used the lookup version. I'm familiar with all of these formulas, I didn't realize that they would round. Usually i use index(match. So when I end with ,false) is that why usually when I use the lookup it returns a #N/A! if it can't find the value? and otherwise it would just round?

The lookup version seemed the best version and gave me the right results.

Thanks.
 

Forum statistics

Threads
1,136,969
Messages
5,678,890
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top