Array

[Skin Paul]

Hi, Back again with another problem. Using xl 2004 version 11.3.5
The formula below entered as an array(Jik being my data) returns the
Closest match. Please what would I change in the formula to get
not the closest but the data value that is preceding my Target value
E.G Data 100,200,300,400,500. Target value entered 361 returns 400.
If I enter a target value between 300 and 400 would like the array to
Return 300. Hope this is clear.
Thanks Paul

=INDEX(Jik,MATCH(SMALL(ABS(Target-Jik),1),ABS(Target-Jik),0))

 

[Bob Greenblatt]

Hi, Back again with another problem. Using xl 2004 version 11.3.5
The formula below entered as an array(Jik being my data) returns the
Closest match. Please what would I change in the formula to get
not the closest but the data value that is preceding my Target value
E.G Data 100,200,300,400,500. Target value entered 361 returns 400.
If I enter a target value between 300 and 400 would like the array to
Return 300. Hope this is clear.
Thanks Paul

=INDEX(Jik,MATCH(SMALL(ABS(Target-Jik),1),ABS(Target-Jik),0))

Look in Excel Help. You want to change the 0 as the last parameter in Match
to -1, but you'll need to resort your data.

 

[Skin Paul]

Hi, Thanks for the reply bob. The reason I resorted to an array was that I
did not want to sort my data. I had been using Vlookup successfully but
Sorting was a hassle. So I thought I would try something new.
Thanks again. Paul

 

[CyberTaz]

You don't have to sort the data to use VLOOKUP if you set the optional
argument (Range_lookup) to FALSE, forcing it to look for an exact match.