PDA

View Full Version : Solved: INDEX/MATCH ...VLOOKUP?



gimli
05-17-2010, 07:27 AM
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
05-18-2010, 06:51 AM
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/viewtopic.php?t=302681&sid=81cc9a966bb7e6a4ca2c63523b1396b7

gimli
05-18-2010, 07:37 AM
Ahh very good...got some good leads there.

thanks much

mdmackillop
05-18-2010, 01:28 PM
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
05-19-2010, 04:16 AM
Thanks MD.
Definately a help.