You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Adding value on the next column based on the Pop Up Menu in Numbers

Hi Guys, I have been looking everywhere but can't seems to find the answer that I need. I need help guys.


I'm trying to make a pop up menu where it will automatically insert the value on the next column (Column B2)


I have done the pop up menu but I have no clue on how to insert the value.



My goal is to every time that I select and item on the pop menu, the value on Column B2 will be automatically added based on the Pricelist table.


Any help would be appreciated, I found a discussion about this but I can't understand the instructions (as English is not my first language) so I'm hoping that I can get a solution here as I've been on it for hours


Thank you very much guys

MacBook Pro 13″, macOS 13.1

Posted on Jan 27, 2023 2:08 AM

Reply
Question marked as Top-ranking reply

Posted on Jan 27, 2023 3:25 AM

Hi hanseno,

I would use XLOOKUP for this task.


Formula for B2=XLOOKUP(A2,Pricelist::A,Pricelist::B,"NF",0,1)


XLOOKUP will take the name from cell A2 and search for this in column A of your pricelist if it is in the list it will return the price that is listed in column B of your pricelist. If the fruit is not in the pricelist it will write "NF" in your table. You can replace the NF with everything, I use NF for Not Found.


It must find an exact match and goes from first to last entry in your pricelist.


XLOOKUP - Apple Support


Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.


Hope this will solve your question, please let me know if this worked for you or if something in unclear.


Regards Ralf


4 replies
Question marked as Top-ranking reply

Jan 27, 2023 3:25 AM in response to hanseno

Hi hanseno,

I would use XLOOKUP for this task.


Formula for B2=XLOOKUP(A2,Pricelist::A,Pricelist::B,"NF",0,1)


XLOOKUP will take the name from cell A2 and search for this in column A of your pricelist if it is in the list it will return the price that is listed in column B of your pricelist. If the fruit is not in the pricelist it will write "NF" in your table. You can replace the NF with everything, I use NF for Not Found.


It must find an exact match and goes from first to last entry in your pricelist.


XLOOKUP - Apple Support


Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.


Hope this will solve your question, please let me know if this worked for you or if something in unclear.


Regards Ralf


Jan 27, 2023 3:29 AM in response to hanseno

You can try something like this:





I made a Pop-up menu for column A of Table 1 by selecting cells in that column and choosing Pop-Up Menu as Data Format. When I did that the Pop-Up Menu choices for each cell were populated automatically with Apples, Oranges, etc. (rather than having to input the menu choices in each cell).




The formula in B2, filled down, is:


=XLOOKUP(A2,Pricelist::A,Pricelist::B,0)


Or, if your regions uses , as a decimal separator the formula would be this instead:


=XLOOKUP(A2;Pricelist::A;Pricelist::B;0)


The 0 is what the formula displays if it can't find a match in the Pricelist table. You can change that to "" or "can't find" or whatever you want.



SG

Adding value on the next column based on the Pop Up Menu in Numbers

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.