Solved: INDEX/MATCH ...VLOOKUP?

[gimli]

Hey all,

Im thinking outloud here cause im unsure how to accomplish something. Not sure if INDEX/MATCH will work...im pretty sure VLOOKUP will not..

anyways

I want to pull data from a table if the lookup value falls between 2 values..

Ex.

Table

2 Cortney
2.5 Parviti
2.75 Jerry
3 Amanda

If the lookup value is 2.5 and 3...I want to pull data if the value is >=2 or <=3. I will have the same formula in multble cells because there can be multible matches.

So given the above example
First match = Cortney - place value in cell E1
Secont match = Parvity - place value in cell E2

And so on

VLOOKUP will only pull one instance...Match wont work...
any ideas will be great. Cant seem to get index/match to work with a formula..

thanks

[stanleydgrom]

gimli,

Try:

Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, Optional Nth As Long = 1)

By jonmo1
http://www.mrexcel.com/board2/viewto...2c63523b1396b7

[gimli]

Ahh very good...got some good leads there.

thanks much

[mdmackillop]

I'm not sure if the final result can be obtained from one formula. This is getting there, but no more time just now. I've left in the intermediate stages, but the Column Q formulae do not rely on them

[gimli]

Thanks MD.
Definately a help.