Hi
I have a data set of numbers.
Range is A1:SH602
I am trying to set a formula that will return:
The cells in that range that holds matching value to number in cell A607 (there may be more than one cell with matching value)
In case there are no matching cells, return the cells with closest value.
I tried using this array formula:
=INDEX(A1:SH602,MATCH(MIN(ABS(A1:SH602-A607)),ABS(A1:SH602-A607),0))
But it returns an error
Thanks
Welcome to the forum
Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Try this
=AGGREGATE(15,6,A1:SH602/(AGGREGATE(15,6,ABS(A1:SH602-A607),1)=ABS(A1:SH602-A607)),1)
Thanks!
Look like this formula returns the value itself, not the cell reference . I am looking for the cell reference
Use ADDRESS:
=ADDRESS(INT(AGGREGATE(15,6,(ROW(A1:SH602)*10000+COLUMN(A1:SH602))/(AGGREGATE(15,6,ABS(A1:SH602-A607),1)=ABS(A1:SH602-A607)),1)/10000), MOD(AGGREGATE(15,6,(ROW(A1:SH602)*10000+COLUMN(A1:SH602))/(AGGREGATE(15,6,ABS(A1:SH602-A607),1)=ABS(A1:SH602-A607)),1),10000),4)
Great!
Thanks a lot, this formula returns only the first cell reference, how can i get all the cells that match the criteria?
Are you still using Excel 2016? Or is your system updated in the meantime?
Last edited by HansDouwe; 12-05-2022 at 08:49 AM.
If you are updated to Excel 365, please tryA smaller example of this formula is included in the attached workbook.Formula:
=LET(t,A1:SH602,a,MAKEARRAY(ROWS(t),COLUMNS(t),LAMBDA(x,y,IF(ABS(A607-INDEX(t,x,y))=MIN(MAP(t,LAMBDA(x,ABS(x-A607)))),1000*x+y))),REDUCE("",a,LAMBDA(x,y,IF(y,TEXTJOIN(", ",,x,ADDRESS(y/1000,MOD(y,1000))),x))))
Here is a simplification of my formula in the previous post:
If you are updated to Excel 365, please tryA smaller example of this formula is included in the attached workbook.Formula:=LET(t,A1:SH602,a,MAKEARRAY(ROWS(t),COLUMNS(t),LAMBDA(x,y,IF(ABS(A607-INDEX(t,x,y))=MIN(MAP(t,LAMBDA(x,ABS(x-A607)))),ADDRESS(x,y,4),""))),TEXTJOIN(", ",,a))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks