CODEWITHSUNDEEP
google-sheetsarray-formulas
AquaAxelrod
AquaAxelrod

Reputation: 37

Pulling Values From A Different Row & Column With Array Formula In Google Sheets

I have a long list (10,000+ rows) of projects. However, underneath each project is a list of tasks that vary in number. On another sheet, I have transferred the tasks, however I need to associate them with the project name. I can do this on an individual basis, however because there are so many, I'm looking for an Array Formula alternative.

This is how I am grabbing the project names on an individual basis:

    =FILTER(
    INDIRECT("Sheet1!$A$2:A"&MATCH(A8,Sheet1!$C$2:$C$50,0)), 
    ROW(INDIRECT("Sheet1!$A$2:A"&MATCH(A8,Sheet1!$C$2:$C$50,0)))=
    (MAX(FILTER(ARRAYFORMULA(ROW(INDIRECT("Sheet1!$A$2:A"&MATCH(A8,Sheet1!$C$2:$C$50,0)))),
    INDIRECT("Sheet1!$A$2:A"&MATCH(A8,Sheet1!$C$2:$C$50,0)) <> "Listing",
    ISTEXT(INDIRECT("Sheet1!$A$2:A"&MATCH(A8,Sheet1!$C$2:$C$50,0)))
    ))))

I need to locate the task name, then move over 2 columns to the left, and then up to acquire the project name.

Here is the Google Sheet for reference.

Upvotes: 1

Views: 108

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A8:A, {Sheet1!C3:C, IF(Sheet1!B3:B="",, 
 VLOOKUP(ROW(Sheet1!A3:A), IF(IF(
 ISTEXT(Sheet1!A3:A)*(Sheet1!A3:A<>"listing"), Sheet1!A3:A, )<>"", {ROW(Sheet1!A3:A), IF(
 ISTEXT(Sheet1!A3:A)*(Sheet1!A3:A<>"listing"), Sheet1!A3:A, )}), 2, 1))}, 2, 0)))

enter image description here

Upvotes: 1

Related Questions