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.
Hi and thanks for helping. You have not misunderstood at all. However, in my worksheet if return numbers, not text, despite trying to format etc. Forgive my ignorance, perhaps something silly that I am missing with my very limited knowledge with excel. Was thinking now of a text join to allow showing returns in another worksheet if possible.
![](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(172.8, 53%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBS%3C/text%3E%3C/svg%3E)
Show us the formula you used to generate those results. Use cut and paste; don't retype the formula.
If it is not the same data as your original message, show us that also, including column and row headers.
![](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(169.60000000000002, 94%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDD%3C/text%3E%3C/svg%3E)
If you format 45705 as a date, it is 17 Feb 2025, so you are most likely pointing to a column of dates, and not locations. Possibly in a single cell as a text join
A simple formula like in F2 might not be practical with your long list of names.