Forum Discussion
Formula help VLOOKUP? MATCH? INDEX>
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
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.
HansVogelaar 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?
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?
HansVogelaar so simple! Thank you. Someone kept telling me to use VLOOKUP and I was struggling.
