Formula Help

[CatherineMadden]

I need a formula that will return a value with multiple criteria. If P1 is found in column G then from G? return the value in the cell 3 right and 21 down from G?

Attached workbook with example.

[#2]

@CatherineMadden 

This is complicated. Would it be OK to move the December to February data below the September to November data, instead of being to the right of them?

[#3]

@CatherineMadden 

I created a Lambda called 'TotalPoints' to obtain the totals. There's a bit of moving heaven and earth because of the data arrangement but here it is:

=LAMBDA(Points_Array,Week_array,OP,month,LET(
    F, FILTER(
        Points_Array,
        (arrOPID = OP) * (TEXT(Week_array, "mmmm") = month) * (IFERROR(YEAR(Week_array), 0) = 2023)
    ),
    IFERROR(SUM(F), 0)
))

arrOPID was created to run parallel with the defined items to identify the rows:

=SCAN("", OPID, LAMBDA(a, v, IF(OR(v < 100, ISTEXT(v)), a, v

[#4]

unfortunately, no, everything has to stay where it is. I was hoping it would work if we did like an XLOOKUP paired with the return value be from a V or H Lookup?

[#5]

When I applied this to my file, I could not get it to work.

[#6]

@CatherineMadden 

You may try this as well.

=LET(opRange, $G:$G, opNum, $P4, pMonth, U$3,
data, OFFSET(OFFSET($B$1,MATCH(opNum,opRange,0)-1,0),6,0,24,13),
cData, VSTACK(CHOOSECOLS(data,{1,2,3}),CHOOSECOLS(data,{8,9,10})),
INDEX(cData,XMATCH(pMonth,INDEX(cData,,1))+7,COLUMN(B1)))

[#7]

@CatherineMadden 

=OFFSET(INDIRECT("G"&MATCH($P4,$G:$G,0)),21,COLUMN(C$1))

An alternative could be this formula.

[#8]

@OliverScheurich 

What you have so far is working perfectly. I was trying to apply another criteria I need which is what we have so far, plus a date then return X value. I added it to the workbook and what I'm looking for.

[#9]

@CatherineMadden 

=OFFSET(INDIRECT("G"&MATCH($P4,$G:$G,0)),MATCH(W$3,INDIRECT("I"&MATCH($P4,$G:$G,0)&":I"&MATCH($P4,$G:$G,0)+30),0)-1,5)

You can try this formula which returns the expected results for the sample data in my sheet.