# Google Sheets Search and Display Location

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?

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

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