## Excel "VLOOKUP" to return a table of values

## Excel "VLOOKUP" to return a table of values

(OP)

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.”

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?

## RE: Excel "VLOOKUP" to return a table of values

## RE: Excel "VLOOKUP" to return a table of values

S&T

## RE: Excel "VLOOKUP" to return a table of values

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

## RE: Excel "VLOOKUP" to return a table of values

Is this "table" for visual only or for the purpose of calculations in Excel? I suspect visual only.

If thats the case, you can use the

Camerafeature in addition to Naming the table range.Maybe I misread your requirement.

Where is the Identifier value with respect to other Identifiers??I also see more columns than 8 and more rows than 4.

To reference this table EVERY row needs date. The implied value must be present in the table.Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

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

## RE: Excel "VLOOKUP" to return a table of values

In Python, dictionaries are the excel version of the vlookup function, denoted by these characters "{}". You feed a dictionary a key and value.

See below for an example:

## CODE --> Python

In this example, x is now equal to the "value" keyed to "M3"

## CODE --> python

You can get even fancier with nested dictionaries where you could theoritically organize your data a little more.

something like: z['M3'][Axial]

this could then call just the axial results for 'M3', but the example above should hopefully give you enough to get going if you wanted to explore python

S&T

## RE: Excel "VLOOKUP" to return a table of values

Arg1 references the top-left of the entire table

Arg2 is the row offset of the Identifier

Arg3 is the column offset of the Identifier

Arg4 is the number of rows associated with the Identifier

Arg5 is the number of columns associated with the Identifier

Then use VLOOKUP or INDEX & MATCH on the OFFSET range.

BTW, an actual COPY n PASTE table would be helpful if you actually want to encourage a solution.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

SkipVoughtThat seems like it could work beautifully if I could nest VLOOKUP in the reference argument“Any idiot can build a bridge that stands, but it takes an engineer to build a bridge that barely stands.”

## RE: Excel "VLOOKUP" to return a table of values

sticksandtriangles, thank you for the effort you put into your explanation. I'm sure your solution works rather well too, I'm just a bit too slow to use it just yet . One day soon!

## RE: Excel "VLOOKUP" to return a table of values

It might be something like this...

=OFFSET

(

IdentifierRng

,MATCH(

SelectedIdentifier,IdentifierRng,0)-1,1

,12

,9

)

...IF each block has 12 rows and 9 columns as your example has.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

The source table is a Structured Table named tDATA in the Data tab.

The dashboard is in Sheet2

`2 1 4 5 6 7 8 9 SelectedIdentifier SelectedMember SelectedMemEnd SelectedMM Axial[k] y Shear[k] z Shear[k] Torque [k-in] y-y Moment [k-in] z-z Moment [k-in] A1 B J Max 0.55279 0.2027661 0.4646301 0.27592941 0.129484892 0.805786672`

SelectedIdentifieris user entry in yellowSelectedMemberis user entry in yellowSelectedMemEndIs either 0 or 2 depending on I or J in the yellow user entrySelectedMMIs either 0 or 1 depending on Min or Max in yellow user entryThese last 2 values add to the row offset for the Index function.

Formula in E3 calculates the correct parameter based on the selections.

E3: =INDEX(OFFSET(tDATA[Identifier],MATCH(SelectedIdentifier,tDATA[Identifier],0)-1,1,12,9),

MATCH(SelectedMember,OFFSET(tDATA[Identifier],

MATCH(SelectedIdentifier,tDATA[Identifier],0)-1,1,12,1),0)+SelectedMemEnd+SelectedMM,E1)

The values in row 1 above the Data table headings are column offsets for the INDEX function.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

I almost exclusively use Index & Match. I don't worry about having the lookup column first. It can be anywhere. If I were to make a lookup column, I'd put it to the right of the existing table. I'd make the INDEX range, just the columns of return data.

It's also possible to use the SUMPRODUCT() function IF and only if each row contains lookup values, which a true table ALWAYS does.

=SUMPRODUCT(

(tDATA[Identifier]=SelectedIdentifier)*

(tDATA[Member]=SelectedMember)*

(tDATA[Member End]=SelectedMemEnd)*

(tDATA[MM]=SelectedMM)*

(tDATA[Axial'[k']])

)

Since your table seems to have fixed block sizes, it greatly simplifies specific row lookups where it seems you don't have lookup vaues in each row.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

Change the dropdown in cell K3 to choose desired member reference.

FILTER EXAMPLE.xlsx

EDIT - realise I left out the min/max in raw data. But you get the idea hopefully!

## RE: Excel "VLOOKUP" to return a table of values

ExtractRows(RowID, DataRange, Rows = 1, FirstCol = 1, Cols = 0)

or ExtractD(same input)

The two functions are the same except ExtractRows steps through the first column until it finds the RowID, and ExtractD creates a dictionary of RowIDs and the associated row number.

They both return an array of the number of rows specified in Rows (default 1), starting at FirstCol (default 1) up to the end, or the number of columns specified in Cols. Note that the Rows value should be equal to the spacing of the Member ID text.

With the code as is I doubt there is any advantage in using a dictionary, in fact quite likely it is slower, but it would be easy to extend the code to read a list of Member IDs (not necessarily sequential), and return a single table for all of them. In that case the dictionary would definitely be quicker.

The functions use the new Excel dynamic array functionality. To use in older versions of Excel they need to be re-entered as array functions.

The spreadsheet including open source code can be downloaded from:

http://interactiveds.com.au/software/Extract%20UDF...

If copying and pasting the code to a new spreadsheet note that the ExtractD function requires the Microsoft Scripting Runtime to be enabled, under the VB editor Tools-References menu.

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel "VLOOKUP" to return a table of values

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

## CODE -->

## RE: Excel "VLOOKUP" to return a table of values

Here's how the function looks using my Structured table notation, where I added a helper column [ID] to fill in the Identifier in each row...

=FILTER(tDATA[[Member]:[z-z Moment '[k-in']]],tDATA[ID]=SelectedIdentifier,NA())

...yields...

Then use Agent666 formula to remove the ZEROS.

=IF(

ISBLANK(FILTER(tDATA[[Member]:[z-z Moment '[k-in']]],tDATA[ID]=SelectedIdentifier,NA())),

"",

FILTER(tDATA[[Member]:[z-z Moment '[k-in']]],tDATA[ID]=SelectedIdentifier,NA())

)

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

## CODE -->

## RE: Excel "VLOOKUP" to return a table of values

In an IF() if you're expression is TRUE or FALSE, you don't need an equality...

IF(ISBLANK(a),"",a)

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

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

Same here. :)

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

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel "VLOOKUP" to return a table of values

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

## RE: Excel "VLOOKUP" to return a table of values

## RE: Excel "VLOOKUP" to return a table of values

## RE: Excel "VLOOKUP" to return a table of values

I didn't read it all, but I can see that Let would help make my formulas more readable:

=====================================

(2B)+(2B)' ?

## RE: Excel "VLOOKUP" to return a table of values

## RE: Excel "VLOOKUP" to return a table of values

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

## RE: Excel "VLOOKUP" to return a table of values

Its not a particularly good idea to put data into formulas, although most of us often do out of convenience. As an example...

=MATCH("North",Region,0)

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 Rangeis already a Named Range reference.The

Match Typeis 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,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

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

## RE: Excel "VLOOKUP" to return a table of values

## RE: Excel "VLOOKUP" to return a table of values

=====================================

(2B)+(2B)' ?

## RE: Excel "VLOOKUP" to return a table of values

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.

## RE: Excel "VLOOKUP" to return a table of values

> 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:

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

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

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

## RE: Excel "VLOOKUP" to return a table of values

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/getfile.aspx?folder=...

## RE: Excel "VLOOKUP" to return a table of values

=====================================

(2B)+(2B)' ?

## RE: Excel "VLOOKUP" to return a table of values

## RE: Excel "VLOOKUP" to return a table of values

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

## RE: Excel "VLOOKUP" to return a table of values

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.

## RE: Excel "VLOOKUP" to return a table of values

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-repl...

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

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

Doug Jenkins

Interactive Design Services

http://newtonexcelbach.wordpress.com/

## RE: Excel "VLOOKUP" to return a table of values

If in D11 I have...

D11: =SUM(Length)

...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 namedArea, 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&Widthrange 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,

_{ Just traded in my OLD subtlety... for a NUance!}## RE: Excel "VLOOKUP" to return a table of values

You can use Named Ranges in combination with Vlookup and Arrays (CTRL-SHIFT-ENTER) command.

Attached is a spreadsheet where I call up entire tables using this methodology. I forget exactly how I did it, but I believe I did the following:

1) each table must be exactly the same size.

2) Give each table a named range.

See attached spreadsheet.. I was only attempting to pull up ~8 tables. If you are dumping data out of an FEA program, you probably have a ton of members. Going through and creating a named range for each would probably be too time consuming.

Below is how to work my spreadsheet if you want to see an example....

On the "Case 1 Load 4 Coefficients" sheet, use the "Long Side" and "Short Side" drop downs to pull up the various tables. If you zoom out on the sheet, you can see all the values change.

I think there is a youtube video on how to do the matrix lookups. I'll have to check my viewing history on the home computer though. If I find it, then I'll post the link.

## RE: Excel "VLOOKUP" to return a table of values

## RE: Excel "VLOOKUP" to return a table of values

I gave him one for ya...

Rather than think climate change and the corona virus as science, think of it as the wrath of God. Feel any better?

-Dik

## RE: Excel "VLOOKUP" to return a table of values

There's almost always more than one way to skin a cat, especially if you have a good assortment of useful tools.

Skip,

_{ Just traded in my OLD subtlety... for a NUance!}