Contributor

# Formula Help

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.

8 Replies

# Re: Formula Help

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?

# Re: Formula Help

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`

# Re: Formula Help

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?

# Re: Formula Help

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

# Re: Formula Help

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)))``````

# Re: Formula Help

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

An alternative could be this formula.

# Re: Formula Help

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.

# Re: Formula Help

``=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.