Approx. Matches for VLOOKUP Function 3

[Trenno]

Hi all,

I'm doing a column load run down for a large project with many different floors. To do this I'm using an FEA floor design program where I can extract loads/reactions from with ease.

I've set up a large spreadsheet that essentially collates all the loads and cumulates them at each level.

Now in each floor plate model, the column location remains constant and this data can be extracted from the program in (X,Y) format. I'd like to make sure my spreadsheet recognizes when a column may have shifted slightly in one model as opposed to the other. So when I do my VLOOKUP function it will search for a coordinate but with a tolerance. Say it will return a value when it finds something within 200mm from the X & Y coordinates.

Example:

Column located at (10,5) on each floor plate from Level 1 to Level 10. But on Level 8 the same column is actually positioned at (9.98,4.95). When I extract the data I need the spreadsheet to recognize that this is actually the same column so it won't disregard the load from Level 9.

Anybody have any thoughts? Much appreciated.

 

[SkipVought]

hi,

How about uploading your workbook or at least posting a representative TABLE example that illustrates this issue.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[electricpete]

Vlookup(approximate) is generally only useful if your lookup values are sorted.
Vlookup (approximate) finds the first entry in the able that is GREATER than or equal to your specified lookup value.
So, it will always match itself to the next lowest value in your table. Which is not exactly what you want.
So you need to create a set of dummy coordinates for lookup that will capture your tolerances.

For example if your original table was mytable:
x y
1 one
2 two
3 three
4 four

Let's say your objective is to return the value corresponding to the NEAREST coordinate (i.e. round to the nearest integer in this case).
vlookup(1.9,mytable, 2, true) returns one. But that's now what we want… we want two.

SO… we need to create a new table mytable with dummy coordinate xdummy that when looked up gives the results we want
xdummy x y
0.5 1 one
1.5 2 two
2.5 3 three
3.5 4 four
Now vlookup (1.9,mytable,3,true) returns two. This accomplishes the desired behavior rounding to the nearest x coordinate.


=====================================
(2B)+(2B)' ?

 

[IDS]

Pete's method works well for a lookup on a single column with stepped data, but unless I'm missing something it won't work with 2D (or multi-dimensional) lookups, and if the data is continuous there is a possibility that the round-off will take the nearest match into a different band, resulting in the wrong match.

A reasonably simple way to do a 2D lookup on the spreadsheet is to calculate the distance from the lookup point to each value and find the minimum. You can then use Match to find the row with the exact match, and index to return the values.

A fairly simple UDF will do the job without needing to generate the distances on the spreadsheet, and will work with any number of columns. I have attached a spreadsheet showing both options:

The UDF also has an option to enter a maximum error in the lookup, and to return the matching row number, or the matching values.





Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Trenno]

Hi Doug,

The UDF is a really neat solution. This will help me clean up the data quite nicely.

Thanks for the help.

 

[Trenno]

Hi Doug and everyone else,

Coming at it from a slightly different angle...

I have written a macro to gather every single column location on every level (pasted under each other from lowest basement to highest level) in the column "Overall Column Index".

Then I've got the formula as shown to extract out the exact and unique column locations. This is to find the vertically continuous columns and where each column may start/stop.

I'm wondering if I could build the tolerance into this step. I'd like the formula to find if a location is close (eg. 500mm distance) to a location beneath it, return the 'matched' location that occur first in Column C.

Then as we move up the building, slightly misaligned columns will 'shift' back into a common coordinate.

 

[IDS]

Trenno - could you post your spreadsheet (or e-mail to gmail, dougaj4) and I'll have a look.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[IDS]

More details of spreadsheet and problems with VLookup here:

https://newtonexcelbach.wordpress.com/2016/10/21/nearest-lookup-function/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/