excel '03: find position of number in an array 1

[firegambler]

Hi folks,

could you please help me on the following:

I need to find the position of a value in an unsorted column.

Example given:


index position of
next higher
or same value
_____________________
1 10
2
3 5
4 5
3 9
4 9
5 9
5 9
3 9
1

I am always looking for the position (row) of the next value that is higher than or equal to the value in the first column.
I can't use the MATCH function as it would need to have the values sorted which won't work for my purpose.

Is there a solution to this without using VBA?

Thanks a lot in advance!

 

[xlhelp]

What about the LARGE function?

Member- AAAA Association Against Acronym Abusers
Only here temporarily during job transition....again....but better this time.

 

[SkipVought]

How about this...
[tt]
=MATCH(A1,A2:$A$10,0)+ROW()
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[firegambler]

Thanks for your quick reply.
Unfortunately I don't quite see how this would help as the large function provides me with a value and not a position of a value.

AND I found out that I made a mistake in my description above (marked red below).

What I am trying to do is, when going down the list in the first column, for each of the values in this column I would like to know in which row the next item of same or LOWER value can be found.

 

[firegambler]

How about this...

=MATCH(A1,A2:$A$10,0)+ROW()

Hi, unfortunately this does only find exact matches, but my condition would also be fulfilled if there was a value found that is lower that the current one.

If i have
1
3
4
4
2
3

and I am searching for the next value fulfilling my condition after my first "3" it would be the value "2" in row 5. So I would like to have the "5" returned.

 

[SkipVought]

"Unfortunately I don't quite see how this would help as the large function provides me with a value and not a position of a value."

NOT SO! The formula I posted, did EXACTLY what you ORIGINALLY asked. Your sample SOLUTION was incorrect, for instance 4 has a next position of 6 and not 5.

This looks to me, like a problems that need a coded solution, as you need to search for one or more values IMPLICIT in your requirement.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[xlhelp]

Hi, Skip.


I believe firegambler was questioning my answer.


Member- AAAA Association Against Acronym Abusers
Only here temporarily during job transition....again....but better this time.

 

[xlbo]

If you can;t sort the data so that you can use a non exact lookup then you will need code I think

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Paste this in a MODULE.

Use like a spreadsheet function, referencing the cell to find the next position...

Function test(rng As Range)
    Dim r As Range
    test = ""
    For Each r In Range(Cells(rng.Row + 1, rng.Column), rng.End(xlDown))
        If r.Value <= rng.Value Then
            test = r.Row
            Exit Function
        End If
    Next
End Function

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[firegambler]

Hey Skip,
thanks a ton, works like a charm!

You made my day!