Formula to Return a Cell Name in Google Sheets

formulasgoogle sheets

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.

Best Answer

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

Related Solutions

Google Sheets – Spreadsheet Formula for Assigning Letter Grades

Keep the inequality signs going in the same direction:

=IF(A1<60, "F", IF(A1<70, "D", IF(A1<80, "C", IF(A1<90, "B", "A"))))

The above works fine as long as there aren't too many cases. But if you had many options, C-,C+,B-,B+... it would be better to use a lookup table instead. For example, if you have a lookup table such as the one below, then

=vlookup(A1, $K$1:$L$5)

does the same job as nested IFs. The table is also easier to maintain in case of changes: e.g., you decided to move some cutoffs.

+---+----+---+
|   | K  | L |
+---+----+---+
| 1 |  0 | F |
| 2 | 60 | D |
| 3 | 70 | C |
| 4 | 80 | B |
| 5 | 90 | A |
+---+----+---+

Google Sheets – Define Range in Function Using Cell Value

Use INDIRECT() to get a cell reference by a string, which in your case is coming from a cell. To use the range as defined in cell C2 but refer to a sheet called data, use:

=countif(INDIRECT("data!"&C2, TRUE),A2)

Hopefully I understand the final formula correctly. If not, replace the range reference with the INDIRECT() function. If this does not work, first check your formula with a manual entry of the range. If that works, there is a syntax issue with the INDIRECT() function so try putting

=INDIRECT(C2, TRUE)

in a cell and see what is returned to check that as well.