Jul 12 2021 01:37 AM
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!
Jul 12 2021 12:00 PM
SolutionThat could be
=IFERROR(INDEX(Table1[PRICE], MATCH(E3,Table1[PRODUCT],0)), "not defined")
if the price is the same for each month
Jul 12 2021 12:00 PM
SolutionThat could be
=IFERROR(INDEX(Table1[PRICE], MATCH(E3,Table1[PRODUCT],0)), "not defined")
if the price is the same for each month