3

I have a table like so:

+---+-----+
| A | 100 |
+---+-----+
| B | 125 |
+---+-----+
| C | 454 |
+---+-----+
| A | 234 |
+---+-----+
| A | 343 |
+---+-----+
| B | 222 |
+---+-----+
| C | 754 |
+---+-----+

I want to write a query where I can search for a particular letter under column A and then display the corresponding value and cell location. I wrote a query like this:

=QUERY(A1:B20, "SELECT B WHERE A='B'")

That generates the following results:

+-----+
| 125 |
+-----+
| 222 |
+-----+

But what I want it to display is:

+----+-----+
| A2 | 125 |
+----+-----+
| A6 | 222 |
+----+-----+

Where it shows the location of all the numbers associated with B. How do I change my formula to display cell locations?

Additionally: it would be nice if I can hyperlink the cell locations to jump directly to the spot. Is there a way to dynamically hyperlink the resultant table?

4

You can create another column on the fly and select it with the query:

=query(arrayformula({A:B, "A"&row(A:A)}), "select Col3, Col2 where Col1 = 'B'")

The arrayformula prepares a three-column table, with the third column being cell references. Since this is a table not contained in the sheet, the column references are abstract names Col1, Col2, Col3 instead of letters A,B,...

There are no hyperlinks leading to cell locations in Google Sheets.

  • This worked! But can you explain what exactly is happening with {A:B, "A"&row(A:A)}. I don't understand the syntax or reasons. – Richard Dec 15 '16 at 21:10
  • It's the array notation. For example, {A:B, D:D} means a three-column array which consists of the columns A,B,D in your sheet. In this case, the third column is obtained from a formula "A"&row(A:A) which means: put letter A in front of row number. – user135384 Dec 15 '16 at 21:24

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.