Fill up a cell from / according another cell data

[manonrop]

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!

[#2 · Accepted solution]

@manonrop 

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

[#3]

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!

[#4]

@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)),"")

[#5]

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. 

[#6]

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