Solved: More scanning help

[blumfeld0]

Hello.
In a previous post I was trying to use VLOOKUP to scan for a particular exact value in a column and someone here helped me out. Their solutions worked very nicely.
Now I am trying to do the same thing but for an apporximate value, not an exact value.
The attached excel file should explain exactly what I am trying to do
thank you in advance folks!

[JimmyTheHand]

Hi

I guess you want it with formulas only? If so, see the attached solution. Of course, there might be a better way, and with VBA it would be less complicated.

Jimmy

[blumfeld0]

thank you!!
now i have to go understand the offset and match functions well enough to actually do this for my data set.

[JimmyTheHand]

You are welcome.
Please keep in mind that cells I2 and I3 contain Array Formulas. You may want to learn about them first. Two links:
http://www.cpearson.com/excel/ArrayFormulas.aspx
http://www.ozgrid.com/Excel/arrays.htm

If you need more help, just ask. If not, please mark the thread solved.

Jimmy

[JimmyTheHand]

We had a PM-exchange with blumfeld0, and I decided to post his questions and my answers, because

• they may help others
• I want to upload a file, which I can't do in PM.

1. is there anyway at all to move the contens of column J
810 820 830 etc. to Cell J2, J3, J4 (or K2,K3, K4 etc.)
because right now they are in cell J7, J8 etc.

2. the limits indicated were -10 and 10
and that gave values of 810 to 910 in column J
but what I really need is 800 to 920 (10 units before 810 and 10 units after 920)
so you might not say why not change the lmits to -8 to 8 instead of -10 to 10?
this is because for my next data set
it might be something different maybe -9 to 9 or -7 to 7 or -5 to 5
i dont want to manually change that because i would have to do that literally thousands of times.
is there any way to excel
"hey give me the number in column B that corresponds to the number right above -10 and below 10 in column A"
or "hey excel i need the limits to be the rounded whole number right above -10 and below 10"

#1.
Yes, there is a way, see the attachment. I made a small twist on formulas in column J, and now the returned values are on top. But the formula gives errors for not found values. I could have built an error-checking in, but then the formuly would have been very long and ugly. So I did the error-checking in column K. Note that formulas in column J are now, too, array formulas.

#2.
I'm not sure I get what you need, but I interpreted it as:
- look for the value closest to -10 in column A
- get the value in the same row, in column B
- step up one cell (this will be the lower limit)
- look for the value closest to +10 in column A
- get the value in the same row, in column B
- step down one cell (this will be the upper limit)
- display all values between the limits

To achieve this, I needed only to put a -1 at the end of formula in cell I2, and a +1 at the end of formula in cell I3.

Jimmy