Query another table in Excel

Question

I want to pull all the values from a particular column in another table. My goal is to take a handful of different tables and put particular columns from each of them into a single, collated table.

For example, let's say I have tables about different kinds of objects

FRUITS

name         flavor
banana       savory
orange       sweet
peach        sweet

PETS

name         lifespan
dog          long
fish         short
cat          long

Imagine that I now want to make a third table with the name column from fruits and pets.

COLLATED

name      source 
banana    fruits
orange    fruits
peach     fruits
dog       pets
fish      pets
cat       pets

I tried to install the powerpivot add-in to do this, but I wasn't sure how to do it with a Mac. I'd prefer to use any "table connection" features that Excel offers in case that is possible.

score 3 · Answer 1 · answered Nov 10 '21 at 13:44

3

=LET(w,(tblFruits[name],tblPets[name],tblCars[name]),x,AREAS(w),y,COUNTA(w),z,IFERROR(INDEX(w,1+MOD(SEQUENCE(x*y,,0),y),,1+INT(SEQUENCE(x*y,,0)/y)),""),FILTER(z,z<>""))

Amend the table column names as required, adding in as many as required.

This should work for reasonably small ranges, though x*y could certainly be improved as a lower bound.

Agreed with Ike that a recursive lambda would probably be of help here.

answered Nov 10 '21 at 13:44

Jos Woolley

8,564
2
4
9

Nice solution. I would also replace ```INT(SEQUENCE(x*y,,0)/y)``` with ```QUOTIENT(SEQUENCE(x*y,,0),y)``` to be consistent with the ```MOD``` used to get the row. Maybe even create a new variable so that you don't repeat the sequence. Something like ```=LET(w,(…),x,AREAS(w),y,COUNTA(w),s,SEQUENCE(x*y,,0),z,IFERROR(INDEX(w,1+MOD(s,y),,1+QUOTIENT(s,y)),""),FILTER(z,z<>""))```. +1. It would be nice if you could adapt it to also retrieve the source as the second column as per OP's question and Ike's answer – Cristian Buse Nov 10 '21 at 14:58
this is indeed better - as you only have to adjust `w` if there are more than two, three, ... tables. – Ike Nov 10 '21 at 16:20
@CristianBuse Thanks. Nice call re `QUOTIENT` and `SEQUENCE`. As for the table names, potentially messy, unless you've thought of a nice solution? Without hard-coding the table names as Ike did, if all relevant column names were "name" then you could try `=LET(a,{"Fruits","Pets","Cars"},b,COUNTA(a),c,INDIRECT("tbl"&a&"[name]"),d,MAX(SUBTOTAL(3,c)),e,T(OFFSET(c,SEQUENCE(d,,0),)),f,e&"|"&a,g,SEQUENCE(b*d,,0),h,INDEX(f,1+MOD(g,d),1+QUOTIENT(g,d)),i,FIND("|",h),j,MID(h,i^{0,1}+{0,1},i-{1,0}),FILTER(j,INDEX(j,,1)<>""))` though such indirect, volatile set-ups are far from ideal! – Jos Woolley Nov 10 '21 at 21:09
Of course, that's also assuming that all tables are named consistently "tbl___" – Jos Woolley Nov 10 '21 at 21:24
Correction. That `MID` part was all wrong. `=LET(a,{"Fruits","Pets","Cars"},b,COUNTA(a),c,INDIRECT("tbl"&a&"[name]"),d,MAX(SUBTOTAL(3,c)),e,CELL("contents",OFFSET(c,SEQUENCE(d,,0),)),f,e&"|"&a,g,SEQUENCE(b*d,,0),h,INDEX(f,1+MOD(g,d),1+QUOTIENT(g,d)),i,FIND("|",h),j,MID(h,i^{0,1}+{0,1},i^{1,9}-1),FILTER(j,INDEX(j,,1)<>""))` – Jos Woolley Nov 10 '21 at 22:05
Indeed, I avoid volatile formulas because in my view the gains worth less than the issues that come with using them. An idea would be this: ```=LET(n,{"Fruits","Pets","Cars"},w,(tblFruits[Name],tblPets[Name],tblCars[Name]),y,COUNTA(w),s,SEQUENCE(AREAS(w)*y,,0),q,1+QUOTIENT(s,y),z,CHOOSE({1,2},IFERROR(INDEX(w,1+MOD(s,y),,q),""),INDEX(n,q)),FILTER(z,INDEX(z,0,1)<>""))```. This would be combination of both your and Ike's ideas. I will actually post this as a community answer. – Cristian Buse Nov 11 '21 at 13:19

Cristian Buse · Accepted Answer · 2021-12-06T10:08:34.680

A combination of ideas from both @Ike and @JosWoolley great answers would be this:

=LET(
n,{"Fruits","Pets","Cars"},
w,(tblFruits[Name],tblPets[Name],tblCars[Name]),
y,COUNTA(w),
s,SEQUENCE(AREAS(w)*y,,0),
q,1+QUOTIENT(s,y),
z,CHOOSE({1,2},IFERROR(INDEX(w,1+MOD(s,y),,q),""),INDEX(n,q)),
FILTER(z,INDEX(z,0,1)<>""))

For a new table, the table name would be added to the n variable and the column/range to the w variable without the need to edit the rest of the formula.

Edit #1

Adding more columns can get tricky using this approach but it can be done. For example having an extra 'Price' column in all tables would require something like this:

=LET(
n,{"Fruits","Pets","Cars"},
w,(tblFruits[Name],tblPets[Name],tblCars[Name]),
p,(tblFruits[Price],tblPets[Price],tblCars[Price]),
y,COUNTA(w),
s,SEQUENCE(AREAS(w)*y,,0),
q,1+QUOTIENT(s,y),
z,CHOOSE({1,2,3},IFERROR(INDEX(w,1+MOD(s,y),,q),""),INDEX(n,q),IFERROR(INDEX(p,1+MOD(s,y),,q),"")),
FILTER(z,INDEX(z,0,1)<>""))

where you have an extra p variable and the CHOOSE is updated to reflect the new values. Of course, you could change the order of the columns in the CHOOSE by either changing the order of the 3 parts or by simply changing the numbers in the {1,2,3} array (e.g. {1,3,2}).

How do I add more columns using this solution? Assume there was another column, for example, called price and the pets had a price to adopt and the fruits had a price to buy. I tried adding another variable and called it price and added tblFruits[Price], tblPets[Price] and so on but that errored out — Cauder, Dec 05 '21 at 20:26
I tried adding them to the w variable, but it looks like that appends to the bottom of the new table as opposed to adding in the data as a separate column — Cauder, Dec 05 '21 at 20:28
@Cauder Hi, just added an edit section to address your question. — Cristian Buse, Dec 06 '21 at 10:09

Ike · Answer 3 · 2021-11-07T14:28:31.820

2

I added two tables to a sheet: tblFruits and tblPets.

Then you can put the following formula in any cell on the same sheet or another sheet.

=LET(
     a,CHOOSE({1,2},tblFruits[name],"Fruits"),
     b,CHOOSE({1,2},tblPets[name],"Pets"),
     rowIndex,SEQUENCE(ROWS(a) + ROWS(b)),
     colIndex,SEQUENCE(1,COLUMNS(a)),
     IF(rowIndex<=ROWS(a),
       INDEX(a,rowindex,colIndex),
       INDEX(b,rowindex-ROWS(a),colIndex)
      )
)

The first four rows of the formula are used to retrieve variables that are then used in the final IF-function:

a and b will return "virtual" arrays of each name column plus the "new" column giving the type.

rowIndex returns a single array {1,2,...(number of rows of both tables)}

colIndex returns an array that is build of the number of columns - in this case 2 (name and type)

These variables are used in the IF-formula: Think of it as a For i = 1 to Ubound(rowIndex)-loop.

If the first value from the rowIndex-Array is smaller than the number of rows of tblFruits,

then INDEX-result is based on virtual array a,
if not the rowindex for b is calculated and INDEX-result is based on virtual array b.

The result is a spill-down array - you can use a filter on it. Just add a header row and add filter.

But you won't be able to create a table based on it. Therefore you will have to use VBA to create the combined data.

This would be the formula with a third table:

=LET(
a,CHOOSE({1,2},tblFruits[Name],"Fruits"),
b,CHOOSE({1,2},tblPets[name],"Pets"),
c,CHOOSE({1,2},tblRooms[name],"Rooms"),
rowIndex,SEQUENCE(ROWS(a)+ROWS(b)+ROWS(c)),
colIndex,SEQUENCE(1,COLUMNS(a)),
IF(rowIndex<=ROWS(a),
         INDEX(a,rowIndex,colIndex),
         IF(rowIndex<=ROWS(a) + ROWS(b),
         INDEX(b,rowIndex-ROWS(a),colIndex),
INDEX(c,rowIndex-(ROWS(a)+ROWS(b)),colIndex))))

edited Nov 07 '21 at 14:28

answered Nov 06 '21 at 18:20

Ike

9,580
4
13
29

Please see my edits to the answer – Ike Nov 07 '21 at 12:26
Thanks, I'm gonna add a 50 pt bounty once it becomes available – Cauder Nov 07 '21 at 14:17
My use case has five or ten different tables. Would I update the last line to something like thisH `INDEX(h,rowIndex-(ROWS(a)+ROWS(b) + rows(c) + rows(d) + rows(e) + rows(f) + rows(g)),colIndex))))` – Cauder Nov 07 '21 at 14:19
hmm - i suppose this is where a recursive lambda will come into play - but I haven't done that yet. Here is a good video: https://www.youtube.com/watch?v=L7s6Dni1dG8 - but you need to have to be on the beta chanel of Microsoft 365 – Ike Nov 07 '21 at 14:22
Ok, so I cannot hard code it with something like my example? The recursive lambda sounds great and very fancy and I'm fine with something dumb like what I tried to make – Cauder Nov 07 '21 at 14:24
you can hard code it - but you will get a pretty unreadable and unmaintainable formula – Ike Nov 07 '21 at 14:26
I don't mind! Maybe this is beyond the scope for this answer though – Cauder Nov 07 '21 at 14:27
It won't let me add the bounty for another day, but I will when I can – Cauder Nov 08 '21 at 00:06

Query another table in Excel

3 Answers3

Linked