# Index and Match assistance

#### pedro-egoli

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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Oeldere

##### Well-known Member

a1 = inputcell
b1 = resultcell

Book1
ABCDE
195%3
2
3
40%80%90%100%120%
512345
Cell Formulas
RangeFormula
B1=INDEX(A4:E5,2,MATCH(A1,A4:E4,1))

#### gaz_chops

##### Well-known Member
Try

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

#### pedro-egoli

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

Pedro

#### gaz_chops

##### Well-known Member
You're lookup value should be C4!

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

#### Oeldere

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

#### pedro-egoli

##### Well-known Member
Thanks.
Initial tries did not work will check again in the morning

Pedro

#### gaz_chops

##### Well-known Member
You're welcome, the lookup is the simpler solution.

#### Oeldere

##### Well-known Member

use VLookup

See the example.

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

#### gaz_chops

##### Well-known Member
The lookup does the same without the need for any tables.

Replies
5
Views
250
Replies
4
Views
1K
Replies
1
Views
417
Replies
8
Views
608
Replies
7
Views
328

1,196,010
Messages
6,012,840
Members
441,733
Latest member
MartijnB

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