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]

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

That is true, but to my opinion is working with a table more flexible.

 

[gaz_chops]

I agree if it was a larger table. But even with a table lookup is simpler.

=LOOKUP(F1,$A$1:$B$5)

[TABLE="width: 684"]
<colgroup><col span="2"><col><col span="8"></colgroup><tbody>[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]ACTUAL[/TD]
[TD]*[/TD]
[TD="align: right"]80%[/TD]
[TD="align: right"]68%[/TD]
[TD="align: right"]89%[/TD]
[TD="align: right"]90%[/TD]
[TD="align: right"]95%[/TD]
[TD="align: right"]121%[/TD]
[/TR]
[TR]
[TD="align: right"]80.01%[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]WOULD[/TD]
[TD]*SCORE[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]90.01%[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]120.00%[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[pedro-egoli]

Nearly there though a little tweaking.
Remember I need the formula to give me "<=" or in othewords up to and including the relative percentage.
In the LOOKUP formula that seems to work except for Cell I5 which shows "4" rather than a "5"
Figures below

Excel Workbook
	D	E	F	G	H	I	J
1	SCORE NEEDED	1	2	3	4	5	*
2	HURDLE						>120%
3	TEST FIGURE	79%	89%	99%	109%	119%	121%
4	TEST SHOULD SCORE	1	2	3	4	5	5
5	FORMULA SCORE	1	2	3	4	4	5
LOOKUP

With regard VLOOKUP the "0%" seems to create a problem as shown below; again the up to and including plus the need for 110%

Excel Workbook
	A	B	C	D	E	F	G
1	79%	1	*	*	*	0%	1
2	80%	2	*	*	*	80%	2
3	110%	4	*	*	*	90%	3
4	111%	4	*	*	*	100%	4
5	119%	4	*	*	*	120%	5
6	120%	5	*	*	*	*	*
VLOOKUP

I am most greatful for both your help here .

Pedro

 

[Oeldere]

For the VLookup solution:

For F5 you need to change the value in 110%.

In your example you don't add the value between 110% and 120%, but I expect it needs to be 5 from 110% and up.


In that case the Lookup formula will be:

=LOOKUP(E3,{0,0.81,0.91,1,1.1},{1,2,3,4,5})

 

[gaz_chops]

For the VLookup solution:

For F5 you need to change the value in 110%.

In your example you don't add the value between 110% and 120%, but I expect it needs to be 5 from 110% and up.


In that case the Lookup formula will be:

=LOOKUP(E3,{0,0.81,0.91,1,1.1},{1,2,3,4,5})

Think the lookup should be.

=LOOKUP(E3,{0,0.81,0.91,1.01,1.11},{1,2,3,4,5})

Note in your OP there was no value if = 100, hence the incorrect value returned above.

[TABLE]
<tbody>[TR]
[TD]>90%<100%[/TD]
[TD]>100%<120%[/TD]
[/TR]
</tbody>[/TABLE]

 

[pedro-egoli]

Thank to each of you.
The LOOKUP works well (great minds between you)and Gas_chops I acknowledge it was my error re the 100.Good detective work as i had no idea why it wouod not work with the others.
Oeldere, you are correct in that all numbers ABOVE 110 get a 5.
However, there is still a problem with the numbers
.<=80 should give 1
.<=90 should give 2 (eg 89 would give a 2)
.<=100 should give a 3
.<=110 should give a 4 (eg 109 gives a 4)

The LOOKUP works fine and no need to redo formula for VLOOKUP unless it suits.

Thanks again to each of you

Pedro

 

[gaz_chops]

You're welcome, if your table was larger then I would say Oeldere's solution of having a table would be the better option.

Gaz

 

[Oeldere]

For the VLookup.

In that case the values in column F need to be 0% - 79,9% - 89,9% - 99,9% - 109,9%.

 

[gaz_chops]

For the VLookup.

In that case the values in column F need to be 0% - 79,9% - 89,9% - 99,9% - 109,9%.

Think that should be

[TABLE="width: 130"]
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="class: xl63, width: 65, align: right"]0.0%[/TD]
  [TD="width: 65, align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]80.1%[/TD]
  [TD="align: right"]2[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]90.1%[/TD]
  [TD="align: right"]3[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]100.1%[/TD]
  [TD="align: right"]4[/TD]
 [/TR]
 [TR]
  [TD="class: xl63, align: right"]110.1%[/TD]
  [TD="align: right"]5[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

 

[Oeldere]

gaz_chops

You are totaly right.

Excuses for the inconviency.