Look up using closest match

  • Thread starter Thread starter Keith Budzynski
  • Start date Start date
K

Keith Budzynski

I am using Excel 2002. I trying to use a formula that
looks up the closest match, then returns the value one
column to the right. I tested it with the lookup value
(B1)on the same tab as the range (Column G) and it worked
fine. When I move the formula to a different tab and
change the B1 cell, the formula returns different values
that are not in the offset column.

{=INDIRECT(ADDRESS(ROW('MSA Summary'!$G$6:$G$323)+MATCH
(MIN(ABS(B1-'MSA Summary'!$G$6:$G$323)),ABS(B1-'MSA
Summary'!$G$6:$G$323),0)-1,COLUMN('MSA Summary'!
$G$6:$G$323)+1))}

Any thoughts,
Thanks,
Keith
 
If I understand you correctly, one way:

=INDIRECT("'MSA Summary'!" & ADDRESS(ROW('MSA
Summary'!$G$6:$G$323) + MATCH(MIN(ABS(B1-'MSA
Summary'!$G$6:$G$323)), ABS(B1-'MSA Summary'!$G$6:$G$323),0)-1,
COLUMN('MSA Summary'!$G$6:$G$323)+1))
 
That seems to get me closer, but every field turned out
to be 1. It should be whole numbers between 1 and 10.
This gives me some ideas. I also tried to
convert "G6:G323" to a named range, but no luck.

Thanks,
Keith
 
Back
Top