Issues with Match function, Please Help

[mehtap3]

I have a list of percentages starting from say 5% to 70% in increments of 5%, I would like to use the match function to obtain the row number associated with a particular % in the range. The issue I am having is when I look for 15% it gives me an error (when it should give me 3) but when I look for say 20% it gives me the correct answer as 4. Can you please help, I don't understand why I am getting this error. Its driving me crazy.

I also tried doing a Vlookup by creating a column next to the cells from 1 to say 4, then run a Vlookup for 15%, I get the same error. What am i doing wrong?

 

[Special-K99]

What (currently mystical) error is it giving you?

Post a few percentage values and the value you are looking for and the MATCH formula you are using

 

[Jonmo1]

Welcome to the board..

Can you post the actual formula(s) you tried?

It may be that either the your list of percentages (or the lookup value) are not EXACTLY at the 5 10 15, but perhaps 5.01 or something like that.
And the match is looking for an exact match.

 

[jim may]

Excel 2010

	A	B	C	D	E
<< Row

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]5%[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]55%[/TD]
[TD="bgcolor: #B7DEE8, align: center"]11[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]20%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]25%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]30%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]35%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]40%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]45%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]50%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]55%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]60%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]65%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]70%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D1[/TH]
[TD="align: left"]=MATCH(C1,$A$1:$A$14,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mehtap3]

Thanks the quick reply Jim.
I tried to replicate your exact sheet in my excel, I get the #N/A error again. Do you think its something to do with my excel settings?
It gives me the error when I look for the following: 15%, 40%, 45%, 50%, 55%, 70%.

Its just so strange,

 

[mehtap3]

New to this site, is there a way i can post my excel sheet itself? It gives me the #N/A error.

 

[mehtap3]

Jonmo1,
I made sure everything formats the same way, I am still getting the same errors.

 

[jim may]

Cell A1 is actually the Value .05 Formatted as a %

 

[mehtap3]

Cell A1 is actually the Value .05 Formatted as a %

Another thing I forgot to mention I am trying to make the list of numbers dynamic, so in my sheet I have the starting number (in our case 5%) the increment ( in our case also 5%) to be an input for the list. Can you try that in yours and see if that works for you?

 

[jim may]

I entered into Cell A2 =A1+0.05

and then copied it down to A14

Have to modify formula to: =MATCH(C1,$A$1:$A$14,1)