Excel copy-paste complicated formula

0
votes

Can someone help me to create an excel formula. Thanks everyone in advance.

I have attached 4 screenshots and in that example:

I need to place formula in sheet "Year report". In "Year report" sheet I have a report to show profits for each product per month. This profit data should be taken from different sheets called "September", "October", etc.

  • in in table "Year report" will be total list of available products.
  • in each month sheet can be different amount of products and they will always be sorted by profit, so sequence of product in a sheet "Year report" will be always different to products sequence in months sheets.

Formula should look like as example:

  1. Search in sheet "September" for product "Apple" in range B6:B12, if "Apple" exist then show value from column D and the same row with "Apple", if does not exist place 0.
  2. Search in sheet "September" for product "Pineapple" in range B6:B12, if "Pineapple" exist then show value from column D and the same row with "Pineapple", if does not exist place 0.
  3. Search in sheet "September" for product "Watermelon" in range B6:B12, if "Watermelon" exist then show value from column D and the same row with "Watermelon", if does not exist place 0. etc...

So I will change it per product and month.

Year report

September

October

November

1
excelexcel-formulacopycopy-pastepaste

1 Answers

0
votes

Put the following formula in C6 and drag it to the the other cells:

=iferror(hlookup($B6,indirect(C$5&"!B6:D12"),3,0),0)

indirect takes the value from row 5 (header with the month name) in the current column and turns it into a reference to a sheet.

hlookup finds the product name from the current row on that sheet.

iferror just makes sure the formula returns a 0 in case nothing is found.

Note that if you add more products below row 12, you will need to update the formula accordingly. Change the D12 to whatever is your last row.