Index collumn with row

[alexxl]

In o13,05 i want from sheet 1 af 11 matching column g from sheet 2 with row 3 sheet 1
coll i from sheet 2 with coll a sheet 1

Indexrowcoll.xlsx

[protonLeah]

named range: KPI_Data (refers to): =Sheet1!$D$1:INDEX(Sheet1!$4:$11,0,MATCH("ZZZZZ",Sheet1!$1:$1))
my solution in S2*:

Please Login or Register  to view this content.

* Array formula

[Glenn Kennedy]

Hi.

Sheet 1 is very badly laid out.

Are there other projects (as well as Dacia) on this sheet? If YES, we need a sample with AT LEAST 2 projects.

If there are more than 2, the use of merged cells in column A makes this more difficult. The best sheets contain NO merged cells at all.

The months in row 1 are a mess. More complex formulae will be needed to determine which "1" relates to 2021, 2022 and 2023. Again, the merged cells are MESSY.

Have you flexibility with the layout of sheet1 1?

Upload a more representative sample, as requested above.

[Glenn Kennedy]

OK> I see how the dates work now. However, we still need to get more information regarding OTHER projects in the same sheet.

[CARACALLA]

Sheet2


O2=SUMPRODUCT((Sheet1!$H$3:$AQ$3=Sheet2!$G2)*(Sheet1!$D$4:$D$11=Sheet2!$L2)*(Sheet1!$H$4:$AQ$11))

Copy down

[alexxl]

KPI PAULC.xlsx
My original file from where i have to extract infos.

If it s very dificult it s ok to unmerge tham and repeatdly populate tham in each corespondent cell instead of merging.

i want to keep the merging at least for collumn a?

what is the meaning of MATCH("ZZZZZ",Sheet1!$1:$1)) zzzz inside of the match?

That formula over there is going to take a while to dig and understand it!

[protonLeah]

MATCH("ZZZZZ",Sheet1!$1:$1))
is one of several ways to find the last used row in a column of text. It makes the named range auto-adjust when items are added or removed from the list/column.