# Label cell if between values for list ISSUE!

#### largeselection

##### Active Member
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
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
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
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

...but this does!

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

#### jbeaucaire

##### Well-known Member
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
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.

Replies
1
Views
176
Replies
3
Views
202
Replies
1
Views
148
Replies
3
Views
225
Replies
25
Views
538

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.

### Which adblocker are you using?

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

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