Match function with #N/A error

I think you'll find the values have to be in ascending sequence if you want the value lower when not using an Exact Match

	A	B	C	D	E
1	Value		Search for:	In Row …
2	0		2	2	=MATCH($C2,$A$1:$A$17,1)
3	5		4	2
4	10		6	3
5	15		8	3
6	20		10	4
7	25		12	4
8	30		14	4
9	35		16	5
10	40		18	5
11	45		20	6
12	50		22	6
13	55		24	6
14	60		26	7
15	65		28	7
16	70		30	8
17	75		32	8

Can there be more than one value less than 30? Are you wanting to return the value or the address of the value?

Could the solution be as simple as this. Enter this formula in C16 and copy down to list all values less than E16:

Formula:

Please Login or Register  to view this content.

oh then it will determine whether the cell has less value than 30.

well I was about to copy the data and sort it out in orders, but I think your is way better.

thanks.

In the previous example, if 30 was not in the table, the MATCH would find the next lower value.

See the example below:

	A	B	C	D	E	F	G
1	Value		Search for:	In Row …
2	0		2	8	=MATCH(30,$A$1:$A$17,1)	29	=INDEX($A$1:$A$17,MATCH(30,$A$1:$A$17,1))
3	5		4	2	=MATCH($C3,$A$1:$A$17,1)	0	=INDEX($A$1:$A$17,MATCH($C3,$A$1:$A$17,1))
4	10		6	3		5
5	15		8	3		5
6	20		10	4		10
7	25		12	4		10
8	29		14	4		10
9	35		16	5		15
10	40		18	5		15
11	45		20	6		20
12	50		22	6		20
13	55		24	6		20
14	60		26	7		25
15	65		28	7		25
16	70		30	8		29
17	75		32	8		29
18	80		30	8		29