VideoPhoto

26 October, 2021

Find ADDRESSES of Specific CELL CONTENTS in Excel Workbook

Let's say we are dealing with Excel table several columns wide and hundreds and hundreds, or even thousands of rows long. Just for illustration I'm providing here a small 'fake' table, a fragment of a big one:

Working on such a large table we may need to find addresses of cells containing some specific values, name, date, number, etc., and we need to look for them quickly.

Here are some examples how we can find - using array formulas - addresses of the first occurrences of the LOWEST value in a column and the HIGHEST value.

The array formulas used for the three cases presented above are:

=ADDRESS(MIN(IF(A2:A23=MIN(A2:A23),ROW(A2:A23),"")),COLUMN(A2:A23))                      

=ADDRESS(MIN(IF(A2:A23=MIN(IF(A2:A23<>0,A2:A23)),ROW(A2:A23),"")),COLUMN(A2:A23))  

=ADDRESS(MIN(IF(A2:A23=MAX(A2:A23),ROW(A2:A23),"")),COLUMN(A2:A23))

We enter them in column A and then copy to other relevant columns, here col. D, E and F. As commented, the addresses point to the first occurrences of the values, we are looking for, in specified single columns.

Now we are looking for address of the first occurrence of the longest string in each of the columns, entering this array formula and copying it to the remaining columns:

=ADDRESS(MIN(IF(LEN(A2:A23)=MAX(LEN(A2:A23)),ROW(A2:A23),"")),COLUMN(A2:A23))

As indicated above, we are presented here with arrays showing only ROW numbers (not full addresses), but of all occurrences of specific numbers or strings in each of the columns. The following array formulas are used:

Col. A:  =SMALL(IF(1000=$A$2:$A$23, ROW($A$2:$A$23)-ROW($A$2)+2), ROW(1:1))     

Col. B=SMALL(IF("Yamana"=$B$2:$B$23, ROW($B$2:$B$23)-                       ROW($B$2)+2),ROW(1:1))            

Col. C: =SMALL(IF("Davis"=$C$2:$C$23, ROW($C$2:$C$23)-ROW($C$2)+2), ROW(1:1))

Col. F: =SMALL(IF(2890=$F$2:$F$23, ROW($F$2:$F$23)-ROW($F$2)+2), ROW(1:1))

These array formulas were expanded down the rows until the #NUM! error showed up, indicating the end of array (i.e. no more addresses found).

The row numbers found with array formulas are easily transformed into full cell addresses using standard (not array) formulas (shown below), based on the row numbers found with the array formulas above: 

Col. A:   =ADDRESS(A33,COLUMN(A33))
Col. B:   =ADDRESS(B33,COLUMN(B33))
Col. C:   =ADDRESS(C33,COLUMN(C33)) 
Col. F:    =ADDRESS(F33,COLUMN(F33)) 

Two important notes:

  1. All ARRAY formulas must be entered using CTRL+SHIFT+ENTER keyboard keys.
  2. Dates - with exception of string length case - are treated in comparisons of their values as serial numbers.




No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.