Excel "VLOOKUP" to return a table of values 9

[MegaStructures]

Hello:

I have a situation where I have a native list coming out of a structural analysis program that comes with one identifier for a block of values 8 columns wide and 4 rows tall. I would like to find a way to call the entire table from another sheet by looking for the single identifier. Example of the information is below.

Is there a way to do this with a native excel function? Is there a relatively easy way to do this with VBA or Python?

“Any idiot can build a bridge that stands, but it takes an engineer to build a bridge that barely stands.”

 

[IDS]

I never used the LET() function before. That's TWO I learned from you in this thread! Thanks!

Same here.

Even so, I do think the VBA makes life simpler once set up.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[3DDave]

"LET is a new beta function available in Excel 365 only."

https://exceljet.net/excel-functions/excel-let-function

 

[Agent666]

LET() was added July 2020 (It's not in beta). That article is obviously a bit out of date.

 

[3DDave]

A new feature only in Excel 365? Still current enough.

 

[electricpete]

This thread is a lesson from the masters in how many different and powerful ways there are to get stuff done in excel.

I didn't read it all, but I can see that Let would help make my formulas more readable:
[ul]
[li]If I am pulling values from un-named cells, then I can give them a name in Let to help me remember what they represent. For example building a formula in the first row of a table based on other columns in the same row, I can reference a cell by the name that conveys the meaning of the column, and then copy that let formula all the way down[/li]
[li]If I am building a complicated expression I can break it into smaller named parts with Let for readability.[/li]
[/ul]


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

 

[3DDave]

It's not much different than using VBA to create the formula. Unlike VBA there is no option to place comments into the formula where a user defined function. I think this is Microsoft working towards a fully online/server side version of Excel so that they will be fully subscription based. I expect an effort could be made to create "Let()" as a VBA function for non-365 versions.

 

[electricpete]

> It's not much different than using VBA to create the formula. Unlike VBA there is no option to place comments into the formula where a user defined function.

Thanks I'm in full agreement on the value of vba for readabity/documentation and that was the intent of my comment about the many way to get things done as taught by the masters. IDS and Skip are (to me) the Jedi masters of vba that I learned from 5 or 10 years back.

> I expect an effort could be made to create "Let()" as a VBA function for non-365 versions.

Sounds like a challenge....any volunteers? (mostly kidding). Oddly enough my company's Excel for Microsoft 365 MSO 32-Bit doesn't seem to have Let() yet, although I imagine it's just a matter of time.


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

 

[SkipVought]

If you were to always use Named Ranges and Structured Tables, your sheet formulas would predominantly have intelligible names.

Its not a particularly good idea to put data into formulas, although most of us often do out of convenience. As an example...
[tt]
=MATCH("North",Region,0)
[/tt]
The MATCH function has 3 arguments: Lookup Value, Lookup Range, Match Type.
It would be much better to refer to a Range for the Lookup Value, where the value could be much more easily changed on a sheet rather than but editing a formula.
The Lookup Range is already a Named Range reference.
The Match Type is a literal 0, which is generally acceptable for an exact match, since it is unlikely that a >= or <= Match Type would need to be substituted in this case.

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

 

[electricpete]

You have been preaching named ranges forever and I completely agree. That's why I mentioned "unnamed" cells because it is the exception not the norm. The table scenario that I mentioned is very common for me. For example first column are the dates and times. The 2nd, 3rd, 4th columns might be looked up from our plant computer to retrieve specific plant parameters for each time. Then the columns after that (5th, 6th, 7th, 8th etc) are calculations based on the earlier columns. My habit is to build the formulas in the first row of the table and then copy those down to all the rows below. I don't name the individual cells in the first row (I only use absolute named ranges, which can't be copied down), but Let would let allow me to do build the formulas in the first row with names even though the cells I'm referencing aren't named ranges. At any rate there are many ways to skin these cats, this is where I can see it fitting into my own workflow.

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

 

[3DDave]

One can use relative references to create names that copy down/across. Just name a cell and then remove the "$" from the direction that will vary.

 

[electricpete]

Yes, that would probably be better in most circumstances. Relative named ranges is not something I've used a lot (I typically put the name adjacent to the cell or range I want to name and use the "create from selection" which gives an absolute reference and leaves the name their to remind me). It's good to have options.

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

 

[Agent666]

Electricpete, you should lookup the lambda function that's coming (currently in beta). That's their attempt to implement something 'vba like' in a on sheet user defined function.

YouTube has a few interesting uses for these already with stuff that would have only been possible with vba code.

It's all never going to replace vba, but I think you're maybe right regarding online only versions. The problem with online or mobile versions at the moment is there is no vba implementation. For someone who relies a lot on vba, thats something I cannot live with. But the lambda function at least presumably will work on the online and mobile versions which opens up a lot more possibilities.

I was hoping they would add native support for python. I'm not sure about others opinions but that would be very exciting to me if it was done right.

 

[electricpete]

> One can use relative references to create names that copy down/across.
> Yes, that would probably be better in most circumstances. Relative named ranges is not something I've used a lot

I just played with relative named ranges and they don't seem particularly useful in terms of documenting the referenced cell

Experiment 1:
[ul]
[li]Create a named range for B2 as MyRelativeName[/li]
[li]Create C2 as formula =B2+1 (by pointing to cell B2).[/li]
[li]The resulting formula in C2 is simply =B2+1[/li]
[li]The resulting formula in C2 does not tell us anything about B2 other than its address.[/li]
[/ul]

Experiment 2:
[ul]
[li]Create a named range for $B$3 as MyAbsoluteName[/li]
[li]Create C3 as formula =B3+1 (by pointing to cell B3). [/li]
[li]The resulting formula in C3 is =MyAbsoluteName+1[/li]
[li]The resulting formula in C3 tells us the meaningful name of the referenced cell[/li]
[/ul]

I think I can see why excel does this. If we have a cell formula including a relative named range and we copy that formula into a new cell, assuming we keep the name intact then we have a bunch of different cells all containing formulas including MyRelativeName where each occurrence to MyRelativeName is pointing to a different cell. That would be well-defined if we bear in mind the relative nature of the particular named range, but it seems to present some potential for confusion (especially for me because I am used to thinking of named ranges as absolute references... if you create from selection you get an absolute reference).

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

 

[3DDave]

Create a name "Length" which is $A2 and a name "Width" which is $B2 and a formula in C2 which is Length * Width.

Copy that down in column C and you get a series of formulas which multiply Length * Width from columns A and B.

I've attached an example:

https://files.engineering.com/getfi...1cc-82f6-40e99f18c72e&file=relative_name.xlsx

 

[electricpete]

So the difference between yours and mine is how we accessed the relative named range after it was defined. I pointed to the cell while composing the formula, and it didn't pull up the relative named range (even though pointing to the cell works fine for absolute named range). You typed in the name of the named range into the formula. And you end up with a bunch of formulas that look identical but point to different cells. And in your spreadsheet when we go back to Name Manager to check the definition of the named ranges, that definition depends on where the current cursor is. All of that is probably very logical, but it will take some getting used to, for me being somewhat set in my ways after many years of using absolute named ranges. Thanks for showing there are lots of ways to get things done.




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

 

[3DDave]

Editing the cell shows which cells are referenced and by color which name is referencing the related cell. I removed the "$" while creating the name.

 

[electricpete]

> Create a name "Length" which is $A2 and a name "Width" which is $B2 and a formula in C2 which is Length * Width.

I think it will only work the way you intended if you had placed your cursor somewhere in row 2 before you define those named ranges. When you create named range Width as $B2 it is really looking for the relationship between current cell and row 2, which it will recreate when you later type in that name Width. For example if you were in row 1 when you defined Width as $B2, then the later typing Width into a formula in a cell in row 2 it would refer to B3. That's probably obvious to you, but it's a little new to me.

> Editing the cell shows which cells are referenced and by color which name is referencing the related cell.

That is handy... it will help keep track of what is being referenced.


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

 

[3DDave]

It's relative to where the cell referennces are when the formula that uses them is created.

Interesting - I changed the name references to absolute $A$2 and $B$2 when in cell C2; it updated all the formula cells accordingly. If I moved to C2 and changed it relative $A2 and $B2 then it works as originally. Same with selecting any other formula and changing the reference to match the row#. If there is a row offset, it keeps the row offset.

Nothing particularly unexpected. It would have been nice to have this in 1987.

 

[IDS]

Sounds like a challenge....any volunteers? (mostly kidding). Oddly enough my company's Excel for Microsoft 365 MSO 32-Bit doesn't seem to have Let() yet, although I imagine it's just a matter of time.

My Eval UDF does a similar job to LET, but (at least in my opinion) much more conveniently:

In the screenshot above the function in A29 (F*L^3/(3*E*I)) is evaluated in D29 with: =eval(A29,A31:B34).
Using LET (cell E33) the function is: =LET(F,B31,L,B32,E,B33,I,B34,F*L^3/(3*E*I))

This is my first time using it, but it seems that:
The parameters must be entered directly in the function; you can't refer to a cell address
The values can be a cell address, but each pair of symbols and values must be entered separately, you can't refer to a spreadsheet range listing all the data.
The function to be evaluated must be entered directly in the function.

For more on the Eval function and download link see:

https://newtonexcelbach.com/2017/08/30/udf-to-replace-cell-references-with-values/

For the unit aware EvalU (and other unit conversion functions) see:

https://newtonexcelbach.com/2020/12/29/units4excel-update/

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[SkipVought]

Here's the problem with the relative_name example.

If in D11 I have...
[tt]
D11: =SUM(Length)
[/tt]
...I get ZERO

If I were to select the range C1:C10 > Formulas > Defined Names > Create from selection > Create names from values in the : TOP ROW, now I have a range named Area, and I can SUM(Area) and get 30.

If I were to select A1:B10 and perform the same series of steps of creating names from values in the top row, now those Length & Width range names are meaningful in the workbook.

Don't know what you call what was originally in this workbook, but it is something I never saw or tried. It's interesting, but I can't see a use for it.

Actually, as far as Named Ranges go, since Excel 2007, when Structured Tables were introduced, I use Range Names almost exclusively for single cells and Tables & Lists are all STs. I might use a named range of multiple cells to identify a group of headings, for instance, where I can use INDEX() & MATCH() to pick off individual values within the range or a Data Range that I might want to clear.

One caveat to STs. If I want to make a Data > Validation > List, I'll usually make that a Named Range. If it's a unique list in a ST, I'll create a corresponding Range Name in the Name Manager, using the ST reference. That way the List reference, using the created Named Range, will always be current and the DV List reference won't burp.



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