# Formula that returns a cell name

I'm trying to figure out if it is possible to write a formula that will find some data in a spreadsheet and then return the location (cell column and row) of that data.

For Example

The target data will be somewhere in the range D:F

Column A has a copy of the target data.

Column B needs a formula that will return the location (cell column and row) in range D:F of the target data.

• How Column A (one column) has a copy of the data "somewhere in the range D:F" (three columns)? – Rubén Sep 1 '16 at 23:08
• Yes, the data in column A (1) will be somewhere in the columns D:F (3 columns). I'm trying to use a formula to report 'where' in the spreadsheet the related data is. It will be used for 'human eyes' to find it in a sheet. – Mr. B Sep 7 '16 at 0:36

# Formula

=ARRAYFORMULA(VLOOKUP(FILTER(A:A,LEN(A:A)),SORT({FILTER({D:D,IF(LEN(D:D),"D",)&ROW(D:D)},LEN(D:D));FILTER({E:E,IF(LEN(A:A),"E",)&ROW(A:A)},LEN(A:A));FILTER({F:F,IF(LEN(A:A),"F",)&ROW(A:A)},LEN(A:A))},1,TRUE),2,0))

# Explanation

It's assumed that there are no headings and the there are no blank/empty cells on the data table on columns D, E and F

The formula has the following construct for columns D, E and F, to build a table with two columns, one for the value, the other for the address of the cell containing them.

``````FILTER({D:D,IF(LEN(D:D),"D",)&ROW(D:D)},LEN(D:D))
``````

Each of the tables are joined and sorted

VLOOKUP is used to find the address of the values on column A.

If sheet use headings, change the references on FILTER(A:A,LEN(A:A) to indicate the row at which the data value starts, i.e. replace A:A by A2:A.

=ArrayFormula(vlookup(FILTER(A2:A,LEN(A2:A)),SORT({FILTER({D:D,IF(LEN(D:D),"D",)&ROW(D:D)},LEN(D:D));FILTER({E:E,IF(LEN(A:A),"E",)&ROW(A:A)},LEN(A:A));FILTER({F:F,IF(LEN(A:A),"F",)&ROW(A:A)},LEN(A:A))},1,TRUE),2,0))

ARRAYFORMULA is applied to fill the results column automatically.

IFERROR added to remove the #N/A error if value from A is not present in D,E, or F

=IFERROR(ARRAYFORMULA(VLOOKUP(FILTER(A:A,LEN(A:A)),SORT({FILTER({D:D,IF(LEN(D:D),"D",)&ROW(D:D)},LEN(D:D));FILTER({E:E,IF(LEN(A:A),"E",)&ROW(A:A)},LEN(A:A));FILTER({F:F,IF(LEN(A:A),"F",)&ROW(A:A)},LEN(A:A))},1,TRUE),2,0)),)

• Thanks for this. I will try it this week and get back to you. I really appreciate your explanation as well, it makes sense of the formula. – Mr. B Sep 12 '16 at 17:36
• I couldn't wait to test it. It works, but I have questions and need to tweak it a bit. It works for the 1st entry in column A, but doesn't fill down to work with the rest. I tried tweaking, but I it broke. Also, I will need a header. Again, I tried tweaking and I it broke. Below are links to sheets with and without headers. What do I need to change so it can check agains other rows in column A? `No Header - https://goo.gl/VbwQTC` `With Header - https://goo.gl/JmH56F` – Mr. B Sep 12 '16 at 18:11
• Add arrayformula function. I just updated the answer to include this. – Rubén Sep 12 '16 at 18:20
• Another edit, for the sheet with headers case. – Rubén Sep 12 '16 at 18:26
• @vikarjramun: I rejected your proposed edit because applying code formatting to large formulas make them harder to read and to copy. – Rubén Sep 13 '16 at 22:14