vlookup if value is within a range

[Mathias]

Hi all,
I want to lookup a value that might not match exactly
(ranges +-2), using vlookup. I know one can use true as
last statement, but that won't work. It will always return
a value (the closestone). I want the value returned only
if it is within my range. Dget would work, but I need to
do it for over thaused rows with changeing criteria. Since
Dget always needs at least two rows for criteria, this is
not very elegant.
Any ideas???
Thanks a lot!
Mathias

 

[Vasant Nanavati]

Not sure I fully understand. but perhaps something like the following will
work:

=IF(AND(VLOOKUP(lookup_value,table_array,1,TRUE)>=lookup_value-2,VLOOKUP(loo
kup_value,table_array,column_index_num,TRUE)<=lookup_value+2),VLOOKUP(lookup
_value,table_array,column_index_num,TRUE),"NOT FOUND!")

 

[Barbara]

Hi all,
I want to lookup a value that might not match exactly
(ranges +-2), using vlookup. I know one can use true as
last statement, but that won't work. It will always return
a value (the closestone). I want the value returned only
if it is within my range.

I'm not sure at all...
I call "rng" your range, and $C$1 the ref of the value you're looking up

=IF(SUM(($C$1-2<=rng)*(rng<=$C$1+2)),INDEX(rng,MIN(IF(($C$1-2<=rng)*(rng<=$C
$1+2),ROW(rng)))),"not found")
array formula (to be entered with CTRL+SHIFT+INVIO)

Regards
Barbara