Numbers ...Associating one column with a second column

Question

Level 1

7 points

Numbers ...Associating one column with a second column

I have a spreadsheet with one column with a popup menu of advertisement sizes, which I call "insertions" (1/6th page, 1/3rd page...), and each advertisement size has a specific cost. Is there a way to associate the insertion column with the cost column so it automatically populates the correct cost for the advertisement on my invoice? In this example, I use the Insertions column as my popup menu column. I choose the insertion on my invoice (see bottom image) and I want to automatically populate the cost in the Subtotal column on my invoice.

MacBook Pro 14″, macOS 14.4

Posted on Mar 1, 2024 1:51 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

31,309 points

Mar 1, 2024 3:50 PM in response to wglogowski

It looks like you want to associate column B with the price in column C. It is not clear whether these two tables are on the same sheet. I will assume Table 1 is on Sheet 1 and will write a formula that will work whether they are on the same or different sheets

If Size/Rate is column D

column E formula =IF(D<>"",XLOOKUP(D, Sheet 1::Table 1::B,Sheet 1::Table 1::C,"Not Found",0),"")

You may have done this already (kind of sounds like you have) but I recommend creating the popup menu for column D directly from the column of "insertions" so that there is no possiblity of a typo or additional space character or anything else that would cause a lookup to fail. Plus it is far easier than entering them by hand. Select all the entries in column B, format as pop up menu with "start from blank", copy/paste one of them to the Invoice table, set it to "blank", copy/paste it to the rest of the column.

Reply

Answer 2

wglogowski Author

Level 1

7 points

Mar 1, 2024 6:03 PM in response to Badunit

Thank you so much, that did the trick

Reply

Numbers ...Associating one column with a second column

Similar questions