Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Approx. Matches for VLOOKUP Function

Approx. Matches for VLOOKUP Function

Approx. Matches for VLOOKUP Function

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.


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.

RE: Approx. Matches for VLOOKUP Function


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


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Approx. Matches for VLOOKUP Function

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)' ?

RE: Approx. Matches for VLOOKUP Function

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

RE: Approx. Matches for VLOOKUP Function

Hi Doug,

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

Thanks for the help.

RE: Approx. Matches for VLOOKUP Function

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.

RE: Approx. Matches for VLOOKUP Function

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

Doug Jenkins
Interactive Design Services

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close