Sep 13 2020 06:57 AM
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!
Sep 13 2020 07:19 AM
SolutionIt looks like XLOOKUP or INDEX/MATCH or like shall work, but better to have sample file.
Sep 13 2020 09:05 AM
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!
Sep 13 2020 10:06 AM
If define prices in the table somewhere in the file
price could be taken by
=IFNA(INDEX(tblPrice[Price],MATCH(C6,tblPrice[Item],0)),"")
Sep 14 2020 12:58 AM
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.
Sep 14 2020 02:08 AM
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.
Sep 13 2020 07:19 AM
SolutionIt looks like XLOOKUP or INDEX/MATCH or like shall work, but better to have sample file.