Index and Match assistance

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGH
1*SCORE12345*
2*IF ACTUAL IS>80>90%>100%120%*
3ACTUAL*60%68%89%90%95%121%
4WOULD *SCORE*112235
Sheet1


Thanks
Pedro
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Rearange your table.

a1 = inputcell
b1 = resultcell


Book1
ABCDE
195%3
2
3
40%80%90%100%120%
512345
Blad12
Cell Formulas
RangeFormula
B1=INDEX(A4:E5,2,MATCH(A1,A4:E4,1))
 
Upvote 0
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
BCDEFGHI
1ABCDEFGH
2*SCORE12345*
3*IF ACTUAL IS>80>90%>100%120%*
4ACTUAL*60%68%89%90%95%121%
5WOULD *SCORE*112235
6FORMULA RESULT*445555
Sheet1


Your aassistance (both) much appreciated

Pedro
 
Upvote 0
You're lookup value should be C4!

=LOOKUP(C4,{0,0.81,0.91,1,1.2},{1,2,3,4,5})
 
Upvote 0
I think you don't changed the table like i showed.


Book1
ABCDE
40%80%90%100%120%
512345
6
7
860%1
975%1
1080%2
1185%2
1289%2
1390%3
1495%3
15105%4
16125%5
Blad12
Cell Formulas
RangeFormula
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))
 
Upvote 0
Thanks.
Initial tries did not work will check again in the morning

Pedro
 
Upvote 0
instead of index / match

use VLookup

See the example.


Book1
ABCDEFG
165%10%1
280%2
390%3
4100%4
5120%5
Blad13
Cell Formulas
RangeFormula
B1=VLOOKUP(A1,$F$1:$G$5,2,1)
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

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
Back
Top