×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

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

# Excel "VLOOKUP" to return a table of values9

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

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

Filter function possibly? Base it on another helper column that you populate with the member reference?

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

Python can definitely handle this, I'm sure VBA can as well, but I am not as versed in VBA.

S&T

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

(OP)
I've never used Python, so I assume the solution isn't easy enough to explain to a new beginner?

“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

Hi,

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 Camera feature 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

(OP)
The table is for calculations inside of excel. I basically want to do exactly what VLOOKUP does, but instead of returning a single cell it returns a 8x4 "table" of cells

“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

2
Sorry, I should have followed up with a little more direction.

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

row = [1,2,3,4,5,6]

data = []
data_1 = []
#make dummy data
for i in range(len(row)) :
temp = []
temp_1 = []
for number in row :
temp.append(number*i)
temp_1.append(number*i/2)
data.append(temp)
data_1.append(temp_1)

print(data)
print(data_1)

key = 'M2'
value = data

key_1 = 'M3'
value_1 = data_1
#make dictionary
z = {key: value, key_1: data_1}

#pull values from dictionary
x = z["M3"]

print(x) 

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

#### CODE --> python

x
Out[6]:
[[0.0, 0.0, 0.0, 0.0, 0.0, 0.0],
[0.5, 1.0, 1.5, 2.0, 2.5, 3.0],
[1.0, 2.0, 3.0, 4.0, 5.0, 6.0],
[1.5, 3.0, 4.5, 6.0, 7.5, 9.0],
[2.0, 4.0, 6.0, 8.0, 10.0, 12.0],
[2.5, 5.0, 7.5, 10.0, 12.5, 15.0]] 

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

3
VLOOKUP will not work. You could use the OFFSET() function which returns a table array where...
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

(OP)
SkipVought That 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

(OP)
SkipVought that worked perfectly. If I could give you two stars I would. Thank you.

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!

“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

Sorry, I messed up my initial post.
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

Here's my solution.

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


SelectedIdentifier is user entry in yellow
SelectedMember is user entry in yellow
SelectedMemEnd Is either 0 or 2 depending on I or J in the yellow user entry
SelectedMM Is either 0 or 1 depending on Min or Max in yellow user entry
These 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

You could also make a helper lookup column, concatenating the values Identifier, Member, Member End & MinMax. This assumes each row has these values. In my world they do.

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

2
Check attached for example of how to use filter function with a helper column to populate the member reference for each row.

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

If this is going to be extensively used, a VBA UDF is worth looking into. The screenshot below shows two alternatives:
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.

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

Never used the FILTER() function before. Thanks Agent666!

Skip,

Just traded in my OLD subtlety...
for a NUance!

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

If you want to get rid of the zeros where there was blank cells, you can use the following formula instead in cell K4. I just made the assumption this didn't matter, but if it does...

#### CODE -->

=IF(ISBLANK(FILTER(A4:H41,(I4:I41=$K$3),NA()))=TRUE,"",FILTER(A4:H41,(I4:I41=$K$3),NA()))

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

@MegaStructures, the FILTER() function is the way to go for what you require. Give Agent666 a star!

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...
A	I	Min	0.947037154  0.916894091  0.957291456  0.276063872  0.274173201	 0.227659076
0	0	Max	0.420240908  0.275870899  0.74396205   0.844171513  0.572026821	 0.259715255
0	J	Min	0.898135915  0.262635793  0.038368639  0.302601534  0.963878302	 0.68032514
0	0	Max	0.325279064  0.741348506  0.430194221  0.955667447  0.918950724	 0.711676774
B	I	Min	0.172612524  0.740970498  0.617699581  0.164515084  0.50872077	 0.566751177
0	0	Max	0.834604653  0.831944477  0.631297096  0.000235403  0.847738035	 0.703124522
0	J	Min	0.144087841  0.570690898  0.194335207  0.254440439  0.437130526  0.983627217
0	0	Max	0.552797821  0.202766139  0.464630137  0.275929417  0.129484892	 0.805786672
C	I	Min	0.970223782  0.730071533  0.255492407  0.10658717   0.818650002	 0.129609488
0	0	Max	0.436043856  0.907129179  0.607179359  0.992731254  0.571555825	 0.50964999
0	J	Min	0.366840588  0.483552021  0.588865298  0.408826116  0.643131123	 0.611328346
0	0	Max	0.056254561  0.859224409  0.791834459  0.677159893  0.59593761	 0.539130739


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

or simplify a little further using the LET function to avoid referencing the equation twice (helps if you ever have to update the formula as you then only need to do it once.

#### CODE -->

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

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

@Agent666,

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

#### Quote:

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/

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

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

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

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

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

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:
• 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
• If I am building a complicated expression I can break it into smaller named parts with Let for readability.

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

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

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.

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

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

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

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

=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 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,

Just traded in my OLD subtlety...
for a NUance!

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

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

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

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

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

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.

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

#### Quote (electricpete)

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.

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

Here's the problem with the relative_name example.

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

Just traded in my OLD subtlety...
for a NUance!

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

You can do this in Excel......but I think it would be too time consuming.

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

The way I have always seen this and done it myself was to have your title/identifier search return the cell location. Once you have the cell location, cells nearby return table values based on their physical location relative to that title/identifier. Try indexmatch and offset rather than vlookup.

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

#### Quote (If I could give you two stars I would.)

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

Thanks MegaStructures and dik. It's my birthday tomorrow.

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!

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

#### Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!