Problem with match function

[johs32]

I'm trying to use match, to find the relative position of the smallest number, in a large array of calculated data.

First, I use the "small" function, to find the smallest number in the array. (works fine)
Trying to match that number in the data array, gets me the "#N/A" error.

I am using "match type = 0", to find the exact match.

What can I be doing wrong?

 

[Caleeco]

Are you using CTRL + SHIFT + ENTER to enter the formula as an array formula?

Caleeco

 

[johs32]

I am. It makes no difference, still returns "#N/A".

 

[johs32]

The array consists of unsorted numbers, calculated by excel from other places in the workbook. But as I understand it, this shouldn't make a difference, when looking for a unique, exact match?

 

[FDibbins]

Are you using CTRL + SHIFT + ENTER to enter the formula as an array formula?

Caleeco

If it is just a simple =match(small(range),range,0), then that is not an array formula, and could be entered in a regular way

Johs, what exactly is your formula?
If you are trying to return something based on the smallest value in a range, it should be something like this...

	A​	B​	C​	D​
2​	aa	2​
3​	bb	3​		dd
4​	cc	5​
5​	dd	1​
6​	ee	5​
7​	ff	9​

D3=INDEX($A$2:$A$7,MATCH(SMALL($B$2:$B$7,1),$B$2:$B$7,0))
entered regularly

 

[Jonmo1]

Without seeing the actual formula and data it's impossible to say. These are just guesses.
Another possibility is the Floating Point Precision issue with storing decimal values in binary systems.

Try
=MATCH(ROUND(SMALL(Range,1),2),Range,0)

Change the 2 to whatever level of decimal is appropriate for your range of data

 

[johs32]

If it is just a simple =match(small(range),range,0), then that is not an array formula, and could be entered in a regular way

Thanks, I thought so.

My (simplified) formula looks like this:
=MATCH(SMALL(GU13:GV40;1);GU13:GV40)

Could it be because i'm trying to use an array with multiple columns?
In that case, is there a workaround, for finding the position of the smallest number in a multiple-column array??

 

[Jonmo1]

You said

I am using "match type = 0", to find the exact match.

That is not the case in this formula

=MATCH(SMALL(GU13:GV40;1);GU13:GV40)

When the matchtype argument is omitted, as you have done here, then Match uses the matchtype 1

And yes, it's also because of the 2D range
Match will only work on a 1D range

 

[johs32]

Without seeing the actual formula and data it's impossible to say. These are just guesses.
Another possibility is the Floating Point Precision issue with storing decimal values in binary systems.

Thanks, I thought about that too, but I have tried copying the calculated cells, pasting as values only, and using the formula on this "new" data with no result. Shouldn't that fix it?

 

[johs32]

You said
That is not the case in this formula

Sorry, the 1 was entered by mistake, when I simplified my formula. The correct simplification is as you wrote: =MATCH(SMALL(GU13:GV40;1);GU13:GV40;0)

A