Greater than option (-1) not working in Excel Index Match VBA Formula

[cristopulo]

Hello,

I am using an Index/Match formula and at the last function when I don't want to match the exact value (0 - because it rarely would) or take the lesser value (1 - because it is not as accurate) the formula usually outputs an #N/A! error when I have the input as -1 as to take the greater value to the right.

The formula is inputted in VBA (i have tried it as a regular excel formula as well):

Range("E9").Formula = "=INDEX('Data References'!$S$2:$AG$14,MATCH(B7,'Data References'!$R$2:$R$14,0),MATCH(B13,'Data References'!$S$1:$AG$1,-1))"

Basically the very last bolded # is the one I am having trouble dealing with.

Any ideas to what the problem?

Thanks in Advance!
-Chris

 

[Domski]

It's most likely to be due to your data in S1:AG1 not being sorted correctly.

Dom

 

[cristopulo]

I am sorry, dont think I understand. All the values in the range are in increasing order. The problem for example comes when the reference cell outputs 6,47 and it doesnt take the greater value, only the value to the left instead.

Ill Post an example of the first couple lines of the table (S1 starts at the 2nd Column):


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1235"]
<tbody>[TR]
[TD][/TD]
[TD]3[/TD]
[TD]3,5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]15[/TD]
[TD]18[/TD]
[TD]20[/TD]
[TD]22[/TD]
[TD]28[/TD]
[TD]30[/TD]
[TD]36[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]Incandescent & Halogen[/TD]
[TD]ESB 20
2 Pole[/TD]
[TD]ESB 20
2 Pole[/TD]
[TD]ESB 20[/TD]
[TD]ESB 24[/TD]
[TD]ESB 40
2 Pole[/TD]
[TD]ESB 40
2 Pole[/TD]
[TD]ESB 40
3/ 4 Pole[/TD]
[TD]ESB 40
3/ 4 Pole[/TD]
[TD]ESB 40
3/ 4 Pole[/TD]
[TD]ESB 40[/TD]
[TD]ESB 63
3/ 4 Pole[/TD]
[TD]ESB 63
3/ 4 Pole[/TD]
[TD]ESB 63[/TD]
[TD]ESB 63
4 Pole[/TD]
[TD]ESB 63
4 Pole[/TD]
[/TR]
[TR]
[TD]Fluo. w/ Ball. (single uncomp.)[/TD]
[TD]ESB 20
2 Pole[/TD]
[TD]ESB 20
2 Pole[/TD]
[TD]ESB 20
2 Pole[/TD]
[TD]ESB 20
2 Pole[/TD]
[TD]ESB 20[/TD]
[TD]ESB 24
2 Pole[/TD]
[TD]ESB 24
2 Pole[/TD]
[TD]ESB 24
3/ 4 Pole[/TD]
[TD]ESB 24
3/ 4 Pole[/TD]
[TD]ESB 24
3/ 4 Pole[/TD]
[TD]ESB 24[/TD]
[TD]ESB 40
2 Pole[/TD]
[TD]ESB 40
3/ 4 Pole[/TD]
[TD]ESB 40[/TD]
[TD]ESB 63[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


-Chris

 

[Domski]

For -1 the values need to be in descending order.

Dom

 

[cristopulo]

Ohh Ok, this would make a lot of sense then.

If I flip the table and the values tho and input the -1 format it would give the GREATER value and not the one to the right, correct?

Thanks for your help,
-Chris