Forum Discussion

manonrop's avatar
manonrop
Copper Contributor
Sep 13, 2020
Solved

Fill up a cell from / according another cell data

Hello, I'm struggling with an excel sheet I've created and I hope someone will be able to help me...   I have 2 columns: ITEMS - PRICE   I have a drop down menu for my items and I would like th...
Excel for web
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 13, 2020

    manonrop 

    It looks like XLOOKUP or INDEX/MATCH or like shall work, but better to have sample file.

manonrop's avatar
manonrop
Copper Contributor
Sep 13, 2020

Hi SergeiBaklan !

 

Thank you for your swift reply, I've attached a draft of the sheet here for a better understanding

 

I've noted manually the prices but I would like my prices to go automatically when I select my item.

For example XL bag = 25 ; Classic = 15; Purse = 10 etc.

 

Many thanks for your help!

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 13, 2020

manonrop 

If define prices in the table somewhere in the file

price could be taken by

=IFNA(INDEX(tblPrice[Price],MATCH(C6,tblPrice[Item],0)),"")
  • manonrop's avatar
    manonrop
    Copper Contributor
    Sep 14, 2020

    HEllo SergeiBaklan 

     

    Thank you for your reply. May I ask if you can have a look at my sheet ? 

    I wanted to have the lists on a second tab 

     

    https://docs.google.com/spreadsheets/d/1Yn-e1JY96CMmneKqHbK6Yeb7kEJYAuf-9BDrODEY1OQ/edit?usp=sharing

     

    Thank you for your help. 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Sep 14, 2020

      manonrop 

      That could be

      =IFNA(
  INDEX(
      LISTS!$B$4:INDEX(LISTS!$B$4:$B100,COUNTA(LISTS!$B$4:$B100)),
      MATCH($C6,LISTS!$A$4:INDEX(LISTS!$A$4:$A100,COUNTA(LISTS!$B$4:$B100)),0)
),"")

      Compare to previous formula I added dynamic range not to change the formula if list is expanded.

Resources