Search code examples
excelworksheet-functionformulas

excel - match multiple cells in a range with text from other cells and return a particular cell data

so this is a little complicated so heres an example to try and explain the questions:

current sheet structure

I have 3 columns with data where:

Col A = Date (mm-yy)

Col B = Text (names of products, each name appearing multiple times but each time with a different month/year in corresponding cell in Col A)

Col C = Number (stock)

In Col E = I have a list of all products, each name appearing only once.

Cols F through O have the month/years from March 2016 through Dec 2016

I have entered in a few products stock quantities from Col C transposed per product name for each corresponding month. Do it manually will take forever as the list in Col B is really long as is the list in Col E.

What I want to do is have a formula in each cell from F2 to O600 to do the following taking Atlantic Salmon as an example.

In cell F5 take the text from $E$5, look for it in Col B, when found look at corresponding cell in Col A and if that matches the month/year in F1 then return the value in corresponding cell in Col C.

Similarly, in cell J5 take the text from $E$5, look for it in Col B, when found look at corresponding cell in Col A and if that matches the month/year in J1 then return the value in corresponding cell in Col C.

Need this for the entire range from F2 to O600... please... any help will be very much appreciated.

Thanks

Bijal

Solution

  • Although I am not sure that is the best way to design your stock taking sheet, this will work for what you want: 

    =SUMIFS($C$2:$C$29,$B$2:$B$29,$E5,$A$2:$A$29,F$1)

    You will be able to paste that formula into F5 and use the fill tool to apply to all of them.

    Cheers,

    Joe