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.

Improve this question
2
  • 1
    How Column A (one column) has a copy of the data "somewhere in the range D:F" (three columns)?
    – Rubén - People First ♦
    Sep 1, 2016 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, 2016 at 0:36

1 Answer 1

Reset to default
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)),)

Improve this answer
5
  • 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, 2016 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, 2016 at 18:11
  • 1
    Add arrayformula function. I just updated the answer to include this.
    – Rubén - People First ♦
    Sep 12, 2016 at 18:20
  • 1
    Another edit, for the sheet with headers case.
    – Rubén - People First ♦
    Sep 12, 2016 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 - People First ♦
    Sep 13, 2016 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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