This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(243.2, 35%, 33%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDP%3C/text%3E%3C/svg%3E)
What formula in excel with return all the locations in column A (A2:A22) for each month with values >5 (highlighted in green) and <-5 (red). Expecting nine (9) returns for June 2025.
As always appreciate the help.
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
You are welcome. Enter this formula in cell B24
=LET(z,B2:K22,IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(z)),LAMBDA(a,b,HSTACK(a,FILTER(A2:A22&":"&INDEX(z,,b),((INDEX(z,,b)>5)+(INDEX(z,,b)<-5)))))),,1),""))
Hope this helps.
Hi,
Enter this formula in cell B24
=LET(z,B2:K22,IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(z)),LAMBDA(a,b,HSTACK(a,FILTER(A2:A22,((INDEX(z,,b)>5)+(INDEX(z,,b)<-5)))))),,1),""))
Hope this helps.
Hi, thanks for this. On thinking I now need to show the values along with the the location returns. Possibly in a single cell as a text join so that the months return can be referenced back to in another worksheet.
Balloo -10, Ballykeel -11, Ballyvadden -9, Limavady -26, Magherafelt -11, Northbrook -10, Rathmore -7, Silverhill -9, Sprucefield -8, Strabane -10
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi @Dornan, Paul,
Thank you for posting your question in the Microsoft Q&A forum.
Regarding that you want to find a formula in excel with return all the locations in column A (A2:A22) for each month with values >5 (highlighted in green) and <-5 (red). Expecting nine (9) returns for June 2025.
For this situation, please try to use this formula below:
=FILTER($A$2:$A$22,(B$2:B$22>5)+(B$2:B$22<-5),"")
This returns all locations where the June value is either:
For your June 2025 column, this should return 9 locations:
Because $A$2:$A$22 is locked and B$2:B$22 is relative by column, when you copy it to July, August, etc., Excel will automatically check the corresponding month column.
Could you please confirm if this matches the expected result on your side? If I’ve misunderstood any part of your requirement, feel free to let me know. I’d be happy to adjust the solution accordingly.
Thank you again for your time and understanding. While my initial response may not resolve the issue immediately, I’d like to gather more details about your situation so I can assist you more effectively.
I really appreciate your patience, and I’m here to help. Looking forward to your response!
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(201.6, 3%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EI%3C/text%3E%3C/svg%3E)
You can use also this formula:
=IFNA(TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",, BYCOL(B2:K22, LAMBDA(num, ARRAYTOTEXT(LET(cond, (num > 5) + (num < - 5), FILTER(A2:A22, cond) & ":" & FILTER(num, cond)))))), ", ", ";")), "")
BR
IlirU