0

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.

  • 1
    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
1

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)),)

  • 1
    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
  • 1
    Add arrayformula function. I just updated the answer to include this. – Rubén Sep 12 '16 at 18:20
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.