Excel 2007 : Vlookup w/ variable limits and values?

[CAS]

I am having a problem with Excel and it's (...my) ability to find appropriate values for comparison. I have a large string of data (20000+) that varies both positive and negative. I want to pull the displacement value for the closest magnitude with opposite sign that is next in the data sequence; problem is that the data varies inconsistently.

I have tried vlookup with the true definition (as close is good for me), but with the variable limits of the loading it become a manual and tedious process to change the limits for the vlookup function as I only want it to consider the next closest number, not the rest of the entire set.

I have attached an example sheet with an example and a small set of the actual data for aid in understanding of my problem. What I would like to happen (looking at the example) is to find displacements for cells C2 to C9, using the string of data in rows 17 to 29. Basically, I want the displacement value for -A2, -A3....etc by looking at the data ahead of it. I have a vlookup written that works, but I have to change the array limits for each set (Row 2-9 used Row 9-17, etc) within the sequence. I am just trying to find an easier way.

If anyone has any advice, I would greatly appreciate it.

[DonkeyOte]

The references in the narrative don't quite tie out to the sample but I think I follow ... there or thereabouts.

I gather from your sample that a "data sequence" is determined by the trend of the Loads (?)
ie as and when the trend changes direction this equates to the commencement of an entirely new "data sequence" ?

In terms of results - for each calculation the comparatives are only those within the next data sequence ?

When you say next closest - do you mean either side (ie smallest absolute variance) or variance in a given direction ?

I ask because in your 2nd set you have result for 30 tied to -36 though -28.667 would be the closest point of negative sign (in subsequent data sequence)

[CAS]

My example is pretty poor...I tried to make it more simple and probably made it harder!

Anyways....Yes, I am considering a sequence as either an increase or decrease in loading. The loading is cyclic, so it goes up and down to some predetermined value which varies throughout the time history of the total test.

When looking at a load value on an ascending sequence, I want to pull a displacement on the descending branch that corresponds to a load with the "same" (or least difference) magnitude but opposite sign. Thus, positive loads will be looking for corresponding negative loads with equal magnitude (abs()=abs()) and negative loads will be looking for corresponding positive loads (abs()=abs()). The looking is only to occur in the next sequence, no further.

I can get it to pull the correct value if I determine the sequence limits manually, but I want it to do it automatically...therein lies the problem. Hope this clarifies the issue.

[DonkeyOte]

I guess either I didn't ask my question well enough or I don't understand... both are likely.

I will try and ask the question again though this time by means of an example.

Using the sample file and the example data (we'll ignore "Actual Set") such that inputs are in Col A, expected results for Col A are in Col C (the results themselves sourced from Col B)

We have three sequences:

rows 3:10
rows 11:30
rows 31:51

If we use 2nd data sequence as our input collection (ie A11:A30) we know that our comparative data sequence is A31:A51

Using specific examples to illustrate my query:

A11: 60
C11: -17

A14: 30
C14: -13

In the case of A11 we know that the min. value in our comparative sequence is -50, ie there is no value <= -60 in the set.
The expected Disp. result of -17 is we know associated with the MIN Load value in the sequence, ie -50 (> -60)

In the case of A14 the expected result of -13 is the Disp value associated to Load -36 in the comparative sequence
(-36 being the first value <= -30 in the set)
For use latterly...we also know that -28.6667 also exists in the set (with Disp. value -15) and that this would deemed the absolute variance to criteria load (-30).

My question is/was centred upon the inconsistency of direction as inferred by expected results, C11 uses a Disp. value associated with a Load > inverse of criteria Load whereas C14 does the opposite.

If the calc is meant to be bi-directional then my question would then by why C14 fails to use the closer of the Loads to the inverse of criteria Load (ie -28.6667 rather than -36)

Conversely if not bi-directional then why does A11 have a result at all ?

I am hoping that the above example illustrates the query slightly better than was previously the case ?

[CAS]

I would like it to work in any direction (just to get it to work) at this point. Ideally, I would like it to be bidirectional and interpolate an answer if it falls between two references. That being said, there will be locations when it will not be able to find an answer; they are a fraction of the other and I was planning on addressing them when the time comes.