# Formula help VLOOKUP? MATCH? INDEX>

Occasional Contributor

# 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

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

# Re: Formula help VLOOKUP? MATCH? INDEX>

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.

# Re: Formula help VLOOKUP? MATCH? INDEX>

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

# Re: Formula help VLOOKUP? MATCH? INDEX>

@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?

# Re: Formula help VLOOKUP? MATCH? INDEX>

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?

# Re: Formula help VLOOKUP? MATCH? INDEX>

@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.

# Re: Formula help VLOOKUP? MATCH? INDEX>

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

# Re: Formula help VLOOKUP? MATCH? INDEX>

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

# Re: Formula help VLOOKUP? MATCH? INDEX>

@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.