How do I get cells in one column to autofill based on selection from pop up menu in another column?

You can make a destination table like this:

NOTE: Make sure to name this table "Distance". Double click the text "Table 1" and type "Distance"

Now create the pop-up from column A:

then choose "Pop-Up Menu" from the Data Formatter menu:

Cell A2, copy

create a new table like this,

Now select cell B2 and paste. select the pop-up menu you just pasted and choose the "NONE" option

Now select that same pop-up, copy

Now select the cells B2 thru the last cell in column C, paste

When you travel, select the start location and destination.

Now let's enter the formula to perform the lookup. Select cell D2 in table "Table 2"

Select cell D2, then type (or copy and paste from here) the formula:

=IF(B2=C2, "", OFFSET(Distance::$A$1,MAX(MATCH(C2, Distance::$1:$1,0)−1, MATCH(B2, Distance::A,0)−1),MIN(MATCH(C2, Distance::$1:$1,0)−1, MATCH(B2, Distance::A,0)−1)))

shorthand for this is:

D2=IF(B2=C2, "", OFFSET(Distance::$A$1,MAX(MATCH(C2, Distance::$1:$1,0)−1, MATCH(B2, Distance::A,0)−1),MIN(MATCH(C2, Distance::$1:$1,0)−1, MATCH(B2, Distance::A,0)−1)))

select cell D2, copy

select cells D2 thru the end of column D, paste

You can try a "lookup" something like this:

The formula in C2:

=XLOOKUP(B2,Distances::A,Distances::B,"Not found")

This syntax is like this:

=XLOOKUP(search-value,search-range,return-range,if-not-found,match-type,search-type)

... where match-type and search-type are optional.

SG

MrEd_ wrote:

It took me a while to get my head around what was proposed - both seemed viable, but after more searching I came across VLOOKUP formulas and had a go with that which was simple for me to understand and create.

The XLOOKUP used above is a more modern and more flexible version of VLOOKUP. One of its many advantages over VLOOKUP is that it allows you easily to specify what to specify what to display if it doesn't find an exact match. Highly recommend trying it out. It's actually easier to use than VLOOKUP.

Also, in Numbers it is often better to separate data into separate tables and to use Footer Rows for formulas. That way you can just use column letters where you current have $A$1:$B$3.

SG

OK. Did any of the posts solve your problem?