Autofill a table from data in another table.

Here is one traditional approach that keeps all the formulas in Table 2. Some other methods require a new column of formulas in Table 1. You're probably better off doing this method than the more complicated one I posted first. It is easier to understand.

Table 2::A1 has the word East

Table 2::B1 has a zero

Table 2::A2 =IFERROR(INDEX(Table 1::A,B2),"")

Table 2::B2 =MATCH(A$1,OFFSET(Table 1::B,B1,0,ROWS(Table 1::B)−B1,1),0)+B1

fill down with A2 and B2 to complete the columns

Hide column B

There are more than a few methods for solving this problem. It is the problem of returning all matches for a given search term. Below is a new method. I'll post a traditional method in another post.

Table 2::A1 has the word East in it

Table 2::A2 =IFERROR(INDEX(Table 1::A,(COUNTMATCHES(TEXTBEFORE(SUBSTITUTE(TEXTJOIN("✑✑",FALSE,"✑",Table 1::B,"✑"),"✑"&A$1&"✑","✑✐✑",ROW()−1),"✑✑✐✑✑"),"✑")−1)÷2+2),"")

I recommend using copy/paste to get it into your spreadsheet. The odd looking characters are odd on purpose. I had to use characters that would not be found in the search term or the column being searched. These characters are about as unlikely as I could think of using.

Superjuke wrote:

Thank you. Yes, there is a specific reason I need this data in this way,

Then here's another approach using REGEX.EXTRACT.

=REGEX.EXTRACT(TEXTJOIN("~",1,Table 1::A:B),"(\w+)~"&A$1&"\b",ROW(cell)−1,1)

The TEXTJOIN creates one big long string delimited by ~ that looks like this:

Albert~East~Bob~West~Colby~West~Devon~North~Earl~East~Frank~West~George~East~Harold~East~John~West

The pattern that is assembled within REGEX>EXTRACT matches bits of text before a ~ and after and uses the ( ) to "capture" the bit you want (the name from column A).

More on REGEX.EXTRACT here:

REGEX.EXTRACT - Apple Support

Instead of putting "East" in cell A1 you can put in anywhere you want and change the reference in the formula accordingly.

If you don't want to shorten the table so you don't get the warning triangles then you can wrap the formula in IFERROR, though I usually don't like to do that, as clicking on a triangle can offer helpful information:

SG

Is there a particular reason why you need the subset of the data in another table?

It is easier (and more efficient) to just filter on column B for East in the original table to get your results. To do that, click the column letter and choose Quick Filter...

If you absolutely must have the results in a separate table then you can select the cells in the filtered table, command-c to copy, click once in a cell in an existing destination table, and command-v to paste

SG

OK if filter and copy/paste is not an option you can try this.

It will work for your small example, but you have to check if it is practical for your real task.

C3=IF($B3=C$1,COUNTIF($B$2:$B3,"="&C$1),"")

Drag this formula to column C to F and up and down, $ signs are important to fix the columns / rows.

G3=TEXTJOIN("",1,B3:F3)

Drag to formula to all rows

As a result all rows have now a unique name based on location and counter.

Your table for EAST would look like this

A2=XLOOKUP(B2,Table 1::G,Table 1::A,"-",0,1)

East1 to East?? is just text and must be long enough for all your entries.

If you like you can set a filter on the second table, then the rows with "-" will be hidden.

After everything is working you can hide the support columns, then nobody will get confused.

Hope that will guide you in the right direction.

Regards Ralf

This one worked for my needs, thank you for the help!