Efficient table lookup using MATCH and OFFSET 1

[AELLC]

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!

 

[zelgar]

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

 

[AELLC]

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.

 

[EngineeringEric]

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.

 

[AELLC]

Eric,

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.

 

[EngineeringEric]

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.