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

 

[JoelTXCive]

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.

 

[CWB1]

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.

 

[dik]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]