Formula help VLOOKUP? MATCH? INDEX>

Copper Contributor

Hi There,

I've completed a data query and have a long list in Sheet A with three columns:

Column A are dates

Column B are the same 5 items repeated every five rows, let's say

Revenues

CoGS

Payroll Expense

Admin Expense

Net Income

Column C are the values

Sheet B has the dates across the top row and the items in column B in the first column

I want to search the data in Sheet A and fill in the values in Sheet B to match the date in the top row and the label in the first column.

Can anyone suggest a formula for that?

Thank you

 

8 Replies

@karenynp 

In B2 on Sheet B:

=SUMIFS('Sheet A'!$C$2:$C$21,'Sheet A'!$A$2:$A$21,B$1,'Sheet A'!$B$2:$B$21,$A2)

Change Sheet A to the actual name of the first sheet, and adjust the ranges as needed.

Fill to the right, then down, or vice versa.

@Hans Vogelaar  so simple!  Thank you.  Someone kept telling me to use VLOOKUP and I was struggling.

@Hans Vogelaar sorry another problem.  The data I am referring to in Sheet A is in a data table.  When I point to the table in Sheet A, I get the correct values, but the columns are relative references so I cannot copy them across in Sheet B.   If I try to create a reference using 'Sheet A'!$b$2:$b$109, the sumif does not find the value.  This is my first time using data tables outside of the classroom so I am wondering if there is some trick to referring to table references absolutely.  Or do I need to take the data out of table format?   

@karenynp 

The formula does indeed not work correctly if you use structured table references, but as far as I can tell it should work OK if you use range references even if the data on Sheet A are in a table.

 

Could you attach a sample workbook without sensitive/proprietary data?

@Hans Vogelaar here is a sample  - Sheet 2, Column Z has an example of what I want to see and be able to copy across to other dates.  Sheet A currently has just one year and location but I think that is enough to solve the problem with.

 

@karenynp 

This should work in Z3:

 

=SUMIFS(_2019__7[[Column2]:[Column2]],_2019__7[[Column1]:[Column1]],$A3,_2019__7[[Column3]:[Column3]],Z$1)

 

This can be filled to the right. See the attached version.

The idea is from Absolute Structured References in Excel Table Formulas 

@Hans Vogelaar thanks again for your help!!  It works great!

 

@karenynp Why not use a pivot table. Your data is perfectly structured for it. No need for formulae and you can easily adopt the tables when you enter data for more than one year and/or location.

 

See attached.