How to find the address of content in a table in Excel

First introduce the commonly used search commands. Click Find&Select in the menu, select the Find command, and the search dialog box will pop up. If we want to find the location of [Washing Machine], enter [Washing Machine] in the input box, click Find All, and the search results will appear below. Click one of them, and the selection box will automatically jump to the selected position.

If we want to find something else, such as a refrigerator, we need to enter the refrigerator and then search. So is there such a function: no need to input, directly select the data you want to find in the cell, and then another cell automatically displays the address of the data? The following introduces the use of ADDRESS function.

ADDRESS function application

Function format ADDRESS(row_num,column_num,abs_num,a1,sheet_text).

1. Row_num The row number used in the cell reference.

2. Column_num The column label used in cell references.

3. Abs_num specifies the reference type returned.

For example, the result of ADDRESS(3,2,4) is B3, which means the third row and second column, a relative reference.

The following introduces how to find the address of [Washing Machine]. The formula is:

=ADDRESS(MAX((A2:A7=D2)*ROW(2:7)),MAX((A2:A7=D2)*COLUMN(A:A)),4 ). Since this function formula exists in an array, you need to press the Ctrl Shift Enter key combination to confirm.

MAX((A2:A7=D2)*ROW(2:7)) The return value is the row value. The meaning of this function is that the value in A2:A7 is equal to the D2 value. The row address ranges from row 2 to row 7. It compares (A2:A7=D2) and ROW(2:7) and takes the larger value.

MAX((A2:A7=D2)*COLUMN(A:A)) The return value is the column value. The meaning of this function refers to the column address equal to the D2 value in A2:A7, and its range is Column 1 to Column 1. It compares (A2:A7=D2) and COLUMN(A:A) and takes the larger value.

In this example, the MAX function returns 6 and 1 respectively, which means row 6 and column 1.

Change 4 in the formula to 1 and view the result. It can be seen that 1 represents an absolute reference and 4 represents a relative reference.

Note

(A2:A7=D2)*ROW(2:7) and (A2:A7=D2)*COLUMN(A:A) are arrays , so after editing the function formula, you need to press the Ctrl Shift Enter key combination. If you only press the Enter key, the system will not recognize the array.

The above is the detailed content of How to find the address of content in a table in Excel. For more information, please follow other related articles on the PHP Chinese website!