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


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?



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:


    F, FILTER(
        (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




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?
When I applied this to my file, I could not get it to work.


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})),





An alternative could be this formula.

formula help.JPG



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.



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

point time for.JPG