Complicated Index Match Offset function

[Bob]

Hi All

I have a worksheet that interprets values from a #255-#0 number system
and need to convert them to a 0%-100% number system and visa versa.
#255 = 0% . #250 = 1% and so on.

So I have made 2 columns of numbers, one for each number system so each
value from one number system lines up (closely) with it's corresponding
value from the other. Column R is 0-100% Column S is 255- 0.

I am using this formula now:
=IF(ISERROR(INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1)),"
",INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1))

The problem I am having is that the 255 - 0 column may not have the
exact value I am looking for or visa versa, and the function returns a
blank cell.

How do I change the function to get the closest value either higher or
lower than the exact number? At the most I am missing exact values by
..01. Can OFFSET be added? If so how?

Thanks in advance!

Bob

 

[Harlan Grove]

...
....

How do I change the function to get the closest value either higher or
lower than the exact number? At the most I am missing exact values by
.01. Can OFFSET be added? If so how?

....

It requires entering the whole formula as an array formula. The MATCH call
would change to something like

MATCH(MIN(ABS(List-TargetValue)),ABS(List-TargetValue),0)

 

[Bob]

Thanks Harlan!

How do I tell the function to look in the first or second column?

Bob

 

[Harlan Grove]

How do I tell the function to look in the first or second column?

Your original formula was

=IF(ISERROR(INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1)),
"",INDEX($R$9:$S$3094,MATCH(AV44,$S$9:$S$3094,FALSE),1))

Change it to the array formula

=INDEX($R$9:$S$3094,MATCH(MIN(ABS($S$9:$S$3094-AV44)),
ABS($S$9:$S$3094-AV44),0),1)

 

[Robert_Steel]

Bob
glad it is solved and thanks for the feedback

RES

as a postscript to this it is a good indication of how a vague question
lead to people down different avenues
Harlan actually did what you asked and sorted out your formula. As he has
done for me on a number of occasions.
I ignored your request and took a guess at the underlying problem. This
time I got lucky.

Sorry if this in any way feels like a lecture because in reality it was a
much better post than many out there

The more people who read advice such as the link below the better these
groups will be.
http://cpearson.com/excel/newposte.htm

<no response needed - I have said my piece> <vbg>