How to find a number within a list (near match)

[auto.pilot]

Sorry, I can't post a sample from this computer. I've tried using MATCH and a combination of INDEX MATCH, but this problem has me stumped.

Here's what I have:

Range A3:A102 is populated with numbers 1-100, Range B3:B102 is populated with another set of numbers, in numerical order from largest to smallest. In cell D4, I have a single number which is between the highest and lowest numbers in Column B. In cell D5, I'd like a formula which will return the number in column A (1-100), which corresponds with the closest match in column B. Other: The lookup number (D4) will rarely be an exact match to any number in column B & I'd like the result in my formula cell (D5) to round down the result. Example: If the result is between 48 and 49 in column A, then the formula in D5 would return 48.

Tough to explain without posting a sample, hope I have made myself clear.

Thanks

jim

 

[auto.pilot]

Excel Workbook
	A	B	C	D	E	F	G	H
1
2
3	1	499		Lookup #
4	2	499		460
5	3	497
6	4	494			because 460 is between 456 and 474
7	5	491
8	6	481
9	7	481
10	8	481
11	9	474
12	10	456
13	11	456
14	12	450
15	13	425
16	14	418
17	15	418
18	16	412
19	17	410
20	18	408
21	19	408
22	20	407
Sheet1

 

[VoG]

Try

=INDEX(A3:A22,MATCH(D4,B3:B22,-1))

 

[auto.pilot]

That works.

Using -1 as the match type never occurred to me.

Thanks

jim