SOLVED

Fill up a cell from / according another cell data

%3CLINGO-SUB%20id%3D%22lingo-sub-1661709%22%20slang%3D%22en-US%22%3EFill%20up%20a%20cell%20from%20%2F%20according%20another%20cell%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1661709%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI'm%20struggling%20with%20an%20excel%20sheet%20I've%20created%20and%20I%20hope%20someone%20will%20be%20able%20to%20help%20me...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%202%20columns%3A%3C%2FP%3E%3CP%3EITEMS%20-%20PRICE%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20drop%20down%20menu%20for%20my%20items%20and%20I%20would%20like%20the%20price's%20column%20to%20be%20filled%20up%20automatically%20when%20I%20select%20the%20item.%3C%2FP%3E%3CP%3Eie%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3EITEM%20-%20PRICE%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EGloves%20-%2020%3C%2FP%3E%3CP%3EJacket%20-%2090%3C%2FP%3E%3CP%3Eetc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20about%20the%20IF%20bu%20I%20think%20this%20is%20to%20compare%20data%20right%3F%20I%20have%20a%20list%20of%2010%2F15items%20so%20as%20many%20prices%20to%20match.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1661709%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1661782%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20up%20a%20cell%20from%20%2F%20according%20another%20cell%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1661782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F791913%22%20target%3D%22_blank%22%3E%40manonrop%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20XLOOKUP%20or%20INDEX%2FMATCH%20or%20like%20shall%20work%2C%20but%20better%20to%20have%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1661920%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20up%20a%20cell%20from%20%2F%20according%20another%20cell%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1661920%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20swift%20reply%2C%20I've%20attached%20a%20draft%20of%20the%20sheet%20here%20for%20a%20better%20understanding%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20noted%20manually%20the%20prices%20but%20I%20would%20like%20my%20prices%20to%20go%20automatically%20when%20I%20select%20my%20item.%3C%2FP%3E%3CP%3EFor%20example%20XL%20bag%20%3D%2025%20%3B%20Classic%20%3D%2015%3B%20Purse%20%3D%2010%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 the price's column to be filled up automatically when I select the item.

ie:

ITEM - PRICE

Gloves - 20

Jacket - 90

etc.

 

I thought about the IF bu I think this is to compare data right? I have a list of 10/15items so as many prices to match.

 

Many thanks for your help!

5 Replies
best response confirmed by manonrop (New Contributor)
Solution

@manonrop 

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

Hi @Sergei Baklan !

 

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!

@manonrop 

If define prices in the table somewhere in the file

image.png

price could be taken by

=IFNA(INDEX(tblPrice[Price],MATCH(C6,tblPrice[Item],0)),"")

HEllo @Sergei Baklan 

 

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. 

@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.