Google Sheets Search and Display Location

Question

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?

Answer 1

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.