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


Efficient table lookup using MATCH and OFFSET

Efficient table lookup using MATCH and OFFSET

Efficient table lookup using MATCH and OFFSET

For those who "know" me, I absolutely cannot deal with anything too complicated (which to me is macros, VBA, etc), but my Excel is very functional for my purposes. I like to keep it "low-tech", because if I copy a VBA function from your assistance, I can't figure it out to modify things in a year from now.

Therefore my main .xlsx workbook that automates 99% of my work is about 4.6MB of size, which is fine, my computer handles it very well, and I am constantly upgrading its functionality.

What I am embarking on now is copying table data from a wood connector hardware catalog (Simpson Strong-Tie to you SE's) to further automate my calculations.

I have attached a relatively simple example of this to get your opinions if my methods are fairly efficient, because I will be adding much more extensive tables in the future, with more variables affecting the lookup value.

I am asking you to look at the MATCH and OFFSET formula in Cell L27, it has correctly looked up 7.01, and I am asking you if the steps leading to and including Cell L27 is a properly efficient method.

Cell L30 is really the "bottom line" result, but it is not what I am concerned about.

Thanks in advance!

RE: Efficient table lookup using MATCH and OFFSET

I would make the following changes if you're planning to have a lot more data in the sheet:
Cell J22: =MATCH(L11,B:B,0)-9
Cell J23: =MATCH(L13,C10:C25,0)-1

RE: Efficient table lookup using MATCH and OFFSET

Don't really need that because there are only 2 x 4 and 2 x 6 walls involved, and only those 4 wood species listed in the Simpson hardware catalog. However there will be at least 32 sets of inputs.

What I didn't know is that trick of writing the lookup array for wall type as B:B - very useful to me in the future, so I gave you a star.

RE: Efficient table lookup using MATCH and OFFSET

I did not look into your sheet yet, but this sounds like a good candidate for VLOOKUP or HLOOKUP with the TRUE Condition. You probably would have a VLOOKUP embedded in an HLOOKUP.

RE: Efficient table lookup using MATCH and OFFSET


That is true, but it is just as easy for me to use MATCH and OFFSET.

This whole issue came up years ago because I and some others were only using the VLOOKUP and we were getting stymied trying to do some stuff, and MATCH and OFFSET was easy to get my head around.

RE: Efficient table lookup using MATCH and OFFSET

I agree, I would have to say that i probably default to the LOOKUPs but as you stated it is a personal preference. Thanks for sharing the Match/offset commands. I'll have to add that to the tool bag.

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