Ask Your Question

Is there a way to search an array for a numeric value and return the matching cell address?

asked 2017-01-26 08:16:34 +0200

Freiherr gravatar image

updated 2017-01-26 08:32:24 +0200

Is there a way to search an array, as say, A1:E5 for a numeric value and return the matching cell address?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-01-26 18:03:11 +0200

Lupp gravatar image

updated 2017-01-27 10:46:58 +0200

Since there may be more than one occurrences of the item to search for the result will depend, of course, on the order of going through the range to search. Assuming this should be top down in the first column and then top down again for the subsequent columns from left to right, that the range be B2:I21, and that the value to search for is placed into cell $L$2, the result you want will be returned by the very simple fomula =ADDRESS(ROW(B2)+MOD(MATCH(L2,OFFSET(B2,MOD(ROW(OFFSET(INDIRECT("a1"),0,0,COLUMNS(B2:I21)*ROWS(B2:I21),1))-1,ROWS(B2:I21)),INT((ROW(OFFSET(INDIRECT("a1"),0,0,COLUMNS(B2:I21)*ROWS(B2:I21),1))-1)/ROWS(B2:I21))),0)-1,ROWS(B2:I21)),COLUMN(B2)+INT(MATCH(L2,OFFSET(B2,MOD(ROW(OFFSET(INDIRECT("a1"),0,0,COLUMNS(B2:I21)*ROWS(B2:I21),1))-1,ROWS(B2:I21)),INT((ROW(OFFSET(INDIRECT("a1"),0,0,COLUMNS(B2:I21)*ROWS(B2:I21),1))-1)/ROWS(B2:I21))),0)-1)/ROWS(B2:I21))
(This does actually work!)

Kidding aside. About 2 years ago I did a bit of basic research around the topic. As I did not use the results myself, I no longer remember the details. If you want to proceed with that work you can get the files created during the mentioned campaign from this location.

A new example is attached here.

Since implicit array evaluation is handled slightly different in Apache OpenOffice the examples may not work there.

In addition to the above answer I would like to emphasize that this is a case where we easily get a clearer, more effective, and more reliable solution with the help of a user function coded for the purpose. The general disadvantages of relying on user code are well known. There are cases, however where they are not of inevitable relevance.
See this example.
As soon as the new TEXTJOIN function will be debugged sufficiently, it will also offer a way to get returned all the matches in one run. If you want it I can provide my own implementation of a TEXTJOIN variant which is capable of doing as needed.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-01-26 08:16:34 +0200

Seen: 126 times

Last updated: Jan 27 '17