CODEWITHSUNDEEP
excelexcel-formula
EDS
EDS

Reputation: 2195

Match Function Without Ordered Data

The syntax for the Match function in Excel is: 

=MATCH(Value, Range, Match Type)

where the Match Type is 1 for values less than or equal to the specified value, 0 for an exact match, and -1 for greater than or equal to.

My problem is that for the match type to work, the data needs to be ordered in ascending or descending order (for exact matches, this is not the case). But how can I work around this for data that isn't and can't be ordered? Note that it can't be ordered by values because it's ordered by date; the whole point of me looking for the row number is quantify how long it takes something to double or recover, etc. etc.

For example, from cells A1:A5 there are the following values: c(1,3,5,2,4). I want to find the first instance of a value greater than 2. The formula I use is:=MATCH(2,A1:A5,-1).The return I get is of course #N/A since the data is not ordered by value. The return I want to get is 2, since the 2nd row (3) is greater than the value 2.

Upvotes: 1

Views: 3300

Answers (1)

Gary's Student
Gary's Student

Reputation: 96763

Give this a try:

=MATCH(TRUE,INDEX(C1:C5>2,0),)

enter image description here

(don't forget that final comma)

EDIT#1:

As Ron Rosenfeld points out:

=MATCH(TRUE,C1:C5>2,0)

also works

Upvotes: 2

Related Questions