Index and Match assistance

[pedro-egoli]

Trying to get a formula for cell if a percentage achieved is between certain figures.
From what I can read an Index and Match might do the trick but how?
Copy of a spreadsheet showing an example is below

Excel Workbook
	A	B	C	D	E	F	G	H
1	*	SCORE	1	2	3	4	5	*
2	*	IF ACTUAL IS		>80	>90%	>100%	120%	*
3	ACTUAL	*	60%	68%	89%	90%	95%	121%
4	WOULD *SCORE	*	1	1	2	2	3	5
Sheet1

Thanks
Pedro

 

[Oeldere]

Rearange your table.

a1 = inputcell
b1 = resultcell

Book1
	A	B	C	D	E
1	95%	3
2
3
4	0%	80%	90%	100%	120%
5	1	2	3	4	5
Blad12

Cell Formulas
Range	Formula
B1	=INDEX(A4:E5,2,MATCH(A1,A4:E4,1))

 

[gaz_chops]

Try

=LOOKUP(D3,{0,0.81,0.91,1,1.2},{1,2,3,4,5})

 

[pedro-egoli]

Thanks to you both for your suggestions
Oldere, Nearly there with yours except that apart from up to 80% answer of 1 is correct where the remainder are between numbers they give the incorrect answer to what I want.
eg 95% in A1 gives an answer of 3 whereas it should be 4 .
I think the use "0%" in first reference cell has thrown it out
Also need the 110% to be in the 5 as the numbers i listed are scores.

Gaz-chops
Not sure if I did this the correct way however, attached shows discrepancies

Excel Workbook
	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H
2	*	SCORE	1	2	3	4	5	*
3	*	IF ACTUAL IS		>80	>90%	>100%	120%	*
4	ACTUAL	*	60%	68%	89%	90%	95%	121%
5	WOULD *SCORE	*	1	1	2	2	3	5
6	FORMULA RESULT	*	4	4	5	5	5	5
Sheet1

Your aassistance (both) much appreciated

Pedro

 

[gaz_chops]

ADVERTISEMENT

You're lookup value should be C4!

=LOOKUP(C4,{0,0.81,0.91,1,1.2},{1,2,3,4,5})

 

[Oeldere]

I think you don't changed the table like i showed.

Book1
	A	B	C	D	E
4	0%	80%	90%	100%	120%
5	1	2	3	4	5
6
7
8	60%	1
9	75%	1
10	80%	2
11	85%	2
12	89%	2
13	90%	3
14	95%	3
15	105%	4
16	125%	5
Blad12

Cell Formulas
Range	Formula
B8	=INDEX($A$4:$E$5,2,MATCH($A8,$A$4:$E$4,1))
B9	=INDEX($A$4:$E$5,2,MATCH($A9,$A$4:$E$4,1))
B10	=INDEX($A$4:$E$5,2,MATCH($A10,$A$4:$E$4,1))
B11	=INDEX($A$4:$E$5,2,MATCH($A11,$A$4:$E$4,1))
B12	=INDEX($A$4:$E$5,2,MATCH($A12,$A$4:$E$4,1))
B13	=INDEX($A$4:$E$5,2,MATCH($A13,$A$4:$E$4,1))
B14	=INDEX($A$4:$E$5,2,MATCH($A14,$A$4:$E$4,1))
B15	=INDEX($A$4:$E$5,2,MATCH($A15,$A$4:$E$4,1))
B16	=INDEX($A$4:$E$5,2,MATCH($A16,$A$4:$E$4,1))

 

[pedro-egoli]

ADVERTISEMENT

Thanks.
Initial tries did not work will check again in the morning

Pedro

 

[gaz_chops]

You're welcome, the lookup is the simpler solution.

 

[Oeldere]

instead of index / match

use VLookup

See the example.

Book1
	A	B	C	D	E	F	G
1	65%	1				0%	1
2						80%	2
3						90%	3
4						100%	4
5						120%	5
Blad13

Cell Formulas
Range	Formula
B1	=VLOOKUP(A1,$F$1:$G$5,2,1)

 

[gaz_chops]

The lookup does the same without the need for any tables.