Search code examples
excelmatchvlookupworksheet-function

Function to find value given column and row across multiple worksheets in excel

I have an array of data on excel worksheet1 with month of the year in the first column then each column is a different year. On worksheet2, I have a given year. I need to pull the data value from the cell on the worksheet1 given the year(column header) specified on worksheet2 from a a specified row on worksheet1 (month). I have tried using indirect,address,and vlookup functions and they do not seem to understand that I am trying to call data from both worksheet1 and worksheet2.

Worksheet1 

      1970 1971 1972  
Jan    3   4 5  
Feb    5 6 7  
March  7 8 9

Worksheet2 

Jan  
1971  ??

I need the "4" in worksheet1 to go where the question marks are in worksheet2. (above is just a simple example).

Solution

  • Use:

    =SUMIFS(INDEX(Sheet1!A:Z,0,MATCH(A2,Sheet1!1:1,0)),Sheet1!A:A,A1)

    This assumes that the formula is in B2 on Sheet2 and all data starts in A1 on their relative sheets.

    enter image description here