Excel Formula help!

New Contributor

I am trying to set up an excel workbook that contains multiple sheets. Among them:

1. A sheet that is a master list of products (Column A) and their prices (Column B)

2. 12 identical sheets (one for each month) to record sales of products.


In the monthly sheets, I have set up the Product column (column E) as a dropdown list that draws its information from the Master List sheet (column A). I am trying to figure out the correct formula to use so that once the product has been chosen on a given line in any of the monthly sheets, that product's price will auto-populate onto the monthly sheet (column H, highlighted in yellow for ease) from the information entered into the Master List sheet (column B).


This is a workbook that I will use over and over for different clients. Some will have product lists that are only half a dozen lines long. Others may have product lists that are hundreds of lines long. So I am definitely hoping there is a formula that looks at the entry on the Monthly sheet, compares it to the list on the Master List sheet, and returns the value from the same line, but a different column from the Master List.


Hopefully I've explained that clearly enough!


I'm in Excel v16.46 for Mac, although I don't think that's relevant to formula functions. 

Thanks for your help!

3 Replies
best response confirmed by allyreckerman (Microsoft)


That could be

=IFERROR(INDEX(Table1[PRICE], MATCH(E3,Table1[PRODUCT],0)), "not defined")

if the price is the same for each month

That appears to work! Thanks so much!

@singerchickk , you are welcome