Use Dynamic Range for index match?

So I know there must be an easy way to do this. It is somewhat difficult to explain so I will use an example

For example If i have the spreadsheet:

Apples 1
Apples 4
Apples 5
Bananas 8
Bananas 12
Oranges 9
Oranges 11

I want to have a seperate cell with an input, (Apples/Bananas/Oranges) and another cell which has a number input. Based on which input you have chosen, I want to use a dynamic index match that will find the value closest (less than, noted as "1" as match type in formula) to the input number, searching only within the range of the input.

For clarity: If my input is orange, and number input is 10, this formula should return 9.

Thanks in advance

Assuming:

• fruits list is in Column A (A1:A7 for the sample provided)
• numbers are in Column B (B1:B7 for the sample provided)
• Selected Fruit is in Cell C1,
• Input number is in Cell D1.

Use the following Array Formula:

Formula:

Please Login or Register  to view this content.

Note: Array Formula must be entered/confirmed with [Ctrl] +[Shift] + [Enter]

Jewelsharma,

Thank you for your formula, as this worked. Can youprovide a formula to do the same thing, however this time return the value closest, greater than the input number?

Thanks

try the following Array Formula:

Formula:

Please Login or Register  to view this content.

Trust your original thread has been satisfactorily answered. Please take a moment to mark the thread as SOLVED. Here's how:

Method 1
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Method 2
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save