Index and Match Excel Question.

[DNapoli]

Tab1 "01.31.2021" has a "Total" line in column B. I want to return the values in columns C, D, E ,F,G, and H from the "Total Line" in my second tab.
Tab2 "Metrics V3" starts in column L 17 and goes down. I want to populate the totals from the 1st sheet into the 2nd sheet (Metrics V3)
My formula does not drag down in the 2nd sheet. I had to edit it to get it to work manually. What am I doing incorrectly?
=INDEX('01.31.2021'!$C:$H, MATCH("Total", '01.31.2021'!$B:$B, 0) + ROW() - ROW($L$17), COLUMN() - COLUMN($L$17) + 1)
=INDEX('01.31.2021'!$C:$H, MATCH("Total", '01.31.2021'!$B:$B, 0) + ROW(L18) - ROW($L$18), COLUMN() - COLUMN($L$18) + 2)
=INDEX('01.31.2021'!$C:$H, MATCH("Total", '01.31.2021'!$B:$B, 0) + ROW(L19) - ROW($L$19), COLUMN() - COLUMN($L$19) + 3)
View attachment 108883

01.31.2021 Tab

Metrics V3 Tab

 

[KRice]

Your column indexing isn't correct. Try this:

=INDEX('01.31.2021'!$C:$H, MATCH("Total", '01.31.2021'!$B:$B, 0), ROWS(L$17:L17))

As you drag the formula down from L$17, the ROWS(L$17:L17) will increase by 1, giving 1, 2, 3,..., which means you'll return the 1st, 2nd, 3rd,...columns of '01.31.2021'!$C:$H.
Since you have Excel 365, if you want to deliver all of the results with a single "spilling" formula, enter this is L17:

=TOCOL(FILTER('01.31.2021'!$C:$H, '01.31.2021'!$B:$B="Total"))

 

[Peter_SSs]

if you want to deliver all of the results with a single "spilling" formula, enter this is L17:

.. provided '01.31.2021' only has one "Total" row. Also, filter on an entire column like that is relatively slow compared to a MATCH type function.

This would be my suggestion for L17 on 'Metrics V3'

=TRANSPOSE(INDEX('01.31.2021'!C:H,XMATCH("Total",'01.31.2021'!B:B),0))

 

[DNapoli]

Your column indexing isn't correct. Try this:

=INDEX('01.31.2021'!$C:$H, MATCH("Total", '01.31.2021'!$B:$B, 0), ROWS(L$17:L17))

As you drag the formula down from L$17, the ROWS(L$17:L17) will increase by 1, giving 1, 2, 3,..., which means you'll return the 1st, 2nd, 3rd,...columns of '01.31.2021'!$C:$H.
Since you have Excel 365, if you want to deliver all of the results with a single "spilling" formula, enter this is L17:

[QUOTE="KRice, post: 6168420, member: 22940"]
Your column indexing isn't correct. Try this:
[CODE=xls]=INDEX('01.31.2021'!$C:$H, MATCH("Total", '01.31.2021'!$B:$B, 0), ROWS(L$17:L17))

As you drag the formula down from L$17, the ROWS(L$17:L17) will increase by 1, giving 1, 2, 3,..., which means you'll return the 1st, 2nd, 3rd,...columns of '01.31.2021'!$C:$H.
Since you have Excel 365, if you want to deliver all of the results with a single "spilling" formula, enter this is L17:

=TOCOL(FILTER('01.31.2021'!$C:$H, '01.31.2021'!$B:$B="Total"))

[/QUOTE]
Thank you so much for your response. This is what seems to work your formula =INDEX('01.31.2021'!$C:$H, MATCH("Total", '01.31.2021'!$B:$B, 0), ROWS(L$17:L17)). I am very impressed as I have been working on this for a long time and could not get it to work!

 

[DNapoli]

I was just curious about the Metrics V3 sheet. I now manually updated the formula for the second column on the "Metrics V3 Sheet" to change the tab name from 1.31.2021 to 2.28.2021 and this works. I am sure it is more complicated to do this through a formula but thought I would ask.

 

[KRice]

It sounds as if you have multiple worksheets that are named after the ends of months? Do you have a list of those tab names somewhere that can be referenced?...or could you put those tab names somewhere above the column in your Metrics V3 sheet where the values are to be returned? If so, then the worksheet name can be referenced with an INDIRECT function. Typically this isn't ideal, as INDIRECT can make the workbook sluggish, but that also depends on the size of the data being accessed.

And are you interested in a "spilling" formula, such as the TOCOL or TRANSPOSE versions that Peter and I offered. The advantage of the spilling versions is that all of your results are delivered with one formula. You wouldn't need to copy the formula down the column...just place it in the uppermost cell.

 

[KRice]

Here is what I'm describing above:
Suppose January's sheet resembles this..."Total" found in B4

MrExcel_20240323.xlsx
	B	C	D	E	F	G	H
1
2
3
4	Total	3	5	6	8	10	32
1.31.2021

Cell Formulas
Range	Formula
H4	H4	=SUM(C4:G4)

And February's sheet resembles this..."Total found in B7.

MrExcel_20240323.xlsx
	B	C	D	E	F	G	H
1
2
3
4
5
6
7	Total	33	55	66	88	1010	1252
2.28.2021

Cell Formulas
Range	Formula
H7	H7	=SUM(C7:G7)

Then the Metrics sheet could contain the worksheet names to be referenced in some helper cells (shown in yellow)...those cells can be hidden from view, although the spelling needs to exactly match the actual worksheet names. Then INDIRECT functions can refer to those helper cells, obtain the worksheet name, and use that name to construct the ranges that need to be examined. Here we are assuming the data resides in columns B:H for all sheets (B used for the "Total" lookup, and C:H for the numeric data). Here is one version using the LET function to break out the definitions for the numeric data block (called "data") and for the lookup column (called "dataB"):

MrExcel_20240323.xlsx
	K	L	M
14	sheet name	1.31.2021	2.28.2021
15
16
17		3	33
18		5	55
19		6	66
20		8	88
21		10	1010
22		32	1252
Metrics V3

Cell Formulas
Range	Formula
L17:M22	L17	=LET(data,INDIRECT(L14&"!C:H"),dataB,INDIRECT(L14&"!B:B"),TRANSPOSE(INDEX(data,XMATCH("Total",dataB),0)))
Dynamic array formulas.

Alternatively, you can incorporate those data formulas directly into the main formula:

MrExcel_20240323.xlsx
	K	L	M
14	sheet name	1.31.2021	2.28.2021
15
16
17		3	33
18		5	55
19		6	66
20		8	88
21		10	1010
22		32	1252
Metrics V3

Cell Formulas
Range	Formula
L17:M22	L17	=TRANSPOSE(INDEX(INDIRECT(L14&"!C:H"),XMATCH("Total",INDIRECT(L14&"!B:B")),0))
Dynamic array formulas.

 

[DNapoli]

I was just curious about the Metrics V3 sheet. I now manually updated the formula for the second column on the "Metrics V3 Sheet" to change the tab name from 1.31.2021 to 2.28.2021 and this works. I am sure it is more complicated to do this through a formula but thought I would ask.

I figured that out too so I am now good! Thank you.

 

[KRice]

You're welcome. Yes...there are a couple of options that access the existing data directly: 1) manually updating the formula to reflect the changing worksheet names, or 2) using INDIRECT and building the references to the worksheet names (shown in my last example). Glad we could help.

 

[DNapoli]

It sounds as if you have multiple worksheets that are named after the ends of months? Do you have a list of those tab names somewhere that can be referenced?...or could you put those tab names somewhere above the column in your Metrics V3 sheet where the values are to be returned? If so, then the worksheet name can be referenced with an INDIRECT function. Typically this isn't ideal, as INDIRECT can make the workbook sluggish, but that also depends on the size of the data being accessed.

And are you interested in a "spilling" formula, such as the TOCOL or TRANSPOSE versions that Peter and I offered. The advantage of the spilling versions is that all of your results are delivered with one formula. You wouldn't need to copy the formula down the column...just place it in the uppermost cell.

Thank you. I will try that! and let you know if it works.