Label cell if between values for list ISSUE!

[largeselection]

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!

 

[Andrew Poulsom]

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]

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...

 

[jbeaucaire]

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
	A	B	C	D	E
1	Tier	Value		Score	Value
2	0	K		30	H
3	10	J		34	H
4	20	I		35	H
5	30	H		90	B
6	40	G		93	B
7	50	F		40	G
8	60	E		64	E
9	70	D
10	80	C
11	90	B
12	100	A
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]

...but this does!

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

 

[jbeaucaire]

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
	A	B	C	D	E
1		Value		Score	Value
2	0	K		30	H
3	10	J		34	G
4	20	I		35	G
5	30	H		90	B
6	40	G		93	A
7	50	F		40	G
8	60	E		64	D
9	70	D
10	80	C
11	90	B
12	100	A
Sheet1

 

[largeselection]

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.