Efficient table lookup using MATCH and OFFSET
Efficient table lookup using MATCH and OFFSET
(OP)
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!
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
Cell J22: =MATCH(L11,B:B,0)-9
Cell J23: =MATCH(L13,C10:C25,0)-1
RE: Efficient table lookup using MATCH and OFFSET
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
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