Index two columns, return associated cell.

[Legacy 287428]

Hello there!

Been a while since I have posted as the forums have been so helpful. I am working on a summary sheet for a personal finance sheet where the data I am pulling from has 7 columns, and as many rows are in a year. Essentially what I am trying to have the cell in question do is query Column A (year), and Column B (week), locate what row that is, and I want one cell to return "Expenditures to date" (Column F), then the second cell returns "Income to date" (Column G).

The example below is a simplified table of what I am using - If I am trying to pull the Annual Cumulative Credit for week 29 of 2020 it should return 1200 even though there are other week 29's in the table.

I had used the following function to return the result moderately successfully, but when I tried linking the data to the cells in the other work sheet I keep getting #N/A. What worked is (H1 is the year, H2 is the week): =INDEX(G2:G4,MATCH(H1&H2,A2:A4&B2:B4,0)) followed by CTRL+SHIFT+ENTER

I am using Windows 10, and Excel 2016 and cant use XLOOKUP unfortunately.

Year	Week	Weekly Debit	Weekly Credit	Annual Cumulative Debit	Cumulative Debit Adjusted Positive	Annual Cumulative Credit
2017	29	20	50	-500	500	800
2018	29	40	55	-600	600	900
2019	29	25	60	-600	600	1000
2020	29	60	65	-700	700	1200

 

[KRice]

Here is one approach:

Book4
	A	B	C	D	E	F	G	H	I
1	Year	Week	Weekly Debit	Weekly Credit	Annual Cumulative Debit	Cumulative Debit Adjusted Positive	Annual Cumulative Credit	2020	<-- Year
2	2017	29	20	50	-500	500	800	29	<-- Week
3	2018	29	40	55	-600	600	900	700	<-- Expenditures to Date
4	2019	29	25	60	-600	600	1000	1200	<-- Income to Date
5	2020	29	60	65	-700	700	1200
Sheet1

Cell Formulas
Range	Formula
H3	H3	=SUMPRODUCT(($A$2:$A$5=$H$1)*($B$2:$B$5=$H$2),$F$2:$F$5)
H4	H4	=SUMPRODUCT(($A$2:$A$5=$H$1)*($B$2:$B$5=$H$2),$G$2:$G$5)

 

[Legacy 287428]

Here is one approach:

Book4
	A	B	C	D	E	F	G	H	I
1	Year	Week	Weekly Debit	Weekly Credit	Annual Cumulative Debit	Cumulative Debit Adjusted Positive	Annual Cumulative Credit	2020	<-- Year
2	2017	29	20	50	-500	500	800	29	<-- Week
3	2018	29	40	55	-600	600	900	700	<-- Expenditures to Date
4	2019	29	25	60	-600	600	1000	1200	<-- Income to Date
5	2020	29	60	65	-700	700	1200
Sheet1

Cell Formulas
Range	Formula
H3	H3	=SUMPRODUCT(($A$2:$A$5=$H$1)*($B$2:$B$5=$H$2),$F$2:$F$5)
H4	H4	=SUMPRODUCT(($A$2:$A$5=$H$1)*($B$2:$B$5=$H$2),$G$2:$G$5)

That worked well, thank you very much for the suggestion! I tried something similar earlier, but forgot to put parenthesis around the Year and Month queries.

 

[jasonb75]

Using a more efficient function that many people seem to have forgotten about recently

=SUMIFS(F2:F5,A2:A5,H1,B2:B5,H2)

 

[KRice]

You're welcome...we're glad to help. Jason's suggestion is even better...more compact and efficient. Thanks for the feedback.