vlookup if value is within a range

M

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
 
V

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!")
 
B

Barbara

Mathias said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top