Using Match function

[sudeepdhar]

Given a data range in ascending order(This condition cannot be changed), how can one use the match() function to find the row no. of a particular no. in the data range that is greater than or equal to the no. that is searched for.

 

[taigovinda]

Welcome to the board!

As you are probably aware, match(value,range,1) will look at the ascending range but will return the row number where it is equal to or LESS than the value you are looking for. This is depicted in the below screenshot in blue. You can adapt it by adding one to the row number, if the value found by match() is not equal to the value sought as shown in red:

Excel Workbook
	A	B	C	D	E
1			justusingmatch		adaptingtogodownarow
2	2		1	3	2
3	4		3	6	3
4	6		3	7	4
5	8
6	10
Sheet1

Cell Formulas
Range	Formula
C2	=MATCH(D2,$A$2:$A$11,1)
E2	=MATCH(D2,$A$2:$A$11,1)+IF(LOOKUP(D2,$A$2:$A$11)=D2,0,1)

 

[jasonb75]

An alternative would be

=MATCH(FALSE,$A$1:$A$5<C1,0)

Where A1:A5 contains the list to find the match, C1 contains the search value.

Confirmed as array formula with Shift Ctrl Enter

 

[sudeepdhar]

The problem with the first solution is that the data you have chosen contains no.s that are different.In case the data is like 2,2,4,4,4,6,6,6,6,8,8,10 then going down a row will not help.

 

[Aladin Akyurek]

The problem with the first solution is that the data you have chosen contains no.s that are different.In case the data is like 2,2,4,4,4,6,6,6,6,8,8,10 then going down a row will not help.

Value to be searched for: 5. What is the result you expect with respect to the sample you mention here?

 

[taigovinda]

I would think you would want to return 6 if you searched for 5 and that data was 2,2,4,4,4,6,6,6,6,8,8,10... If that's true, then both solutions offered seem to work.

My guess is that my solution is significantly faster - but jasonb75's is definitely nicer to look at