Forum Discussion
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
=OFFSET(INDIRECT("G"&MATCH($P4,$G:$G,0)),21,COLUMN(C$1))An alternative could be this formula.
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.
=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.
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)))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- When I applied this to my file, I could not get it to work.
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?
- 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?
