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?

 

[johs32]

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

However, this does not make any difference, the formula still does not work.

 

[Jonmo1]

The 1 isn't the only problem, I didn't notice until after my last post.

It is also due to the 2D range.
Match won't work on a 2D range. Must be either 1row like GU13:GV13, or 1 column like GU13:GU14

How to resolve depends on what result you're expecting, and what you're overall goal is.

You'll need to explain some more about your expectations.

 

[johs32]

You said

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

Aha. Do you have any suggestions on how to use a match-like function in a 2D range?

 

[Jonmo1]

What was your expected result
=MATCH(SMALL(GU13:GV40;1);GU13:GV40;0)

Say the small value was in GU20, what was your expected result?
Say the small value was in GV15, what was your expected result?

 

[Jonmo1]

And what is the 'Real' formula.
Solutions to simplified problems are often not applicable to the 'real' problem, resulting in wasted time/effort.

 

[johs32]

What was your expected result
=MATCH(SMALL(GU13:GV40;1);GU13:GV40;0)

Say the small value was in GU20, what was your expected result?
Say the small value was in GV15, what was your expected result?

Okay, i'll try to explain further. As the data is quite elaborate, I have tried to simplify it.

I have a table, lets say:

123
456
789

What I want is to lookup the smallest value in the table (in the example: 1) and return the position of that value. In the example: A1.

 

[johs32]

And what is the 'Real' formula.
Solutions to simplified problems are often not applicable to the 'real' problem, resulting in wasted time/effort.

I still get the same error-message in the simplified version, though.
The 'real' data is a 100x100 matrix of cells calculated from multiple other cells in the same workbook.

 

[Jonmo1]

So you're actually after the cell address returned like A1 or B3 etc ?
For what purpose? Are you then going to use that address in another formula?
What is that formula? There may be a simpler way.

 

[johs32]

Well, to be more precise, i'm looking for the row and column header.
If I knew the adress to the cell, I could find out the rest, but yes, there might be a simpler way.

D E F
A1 2 3
B4 5 6
C7 8 9

What I want precisely is, in three columns to show the numbers sorted, with the headers to each. For this example, what I want would be:
1 A D
2 A E
3 A F
4 B D
5 B E
6 B F
7 C D
8 C E
9 C F

 

[Jonmo1]

ok, assuming that table is in A1:D4
Also assuming no duplicate small values.

G1 filled down to G9
=SMALL($B$2:$D$4,ROW(B1))

H1 filled down to H9
=INDEX($A$2:$A$4,SUMPRODUCT(($B$2:$D$4=G1)*(ROW($B$2:$D$4)-ROW($B$2)+1)))

I1 filled down to I9
=INDEX($B$1:$D$1,SUMPRODUCT(($B$2:$D$4=G1)*(COLUMN($B$2:$D$4)-COLUMN($B$2)+1)))