CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
kaitlynmm569
kaitlynmm569

Reputation: 1715

How to find a cell with a specific value across the entire sheet

I apologize if this has been asked before. I did my research and nothing I found seemed to be able to answer my question though, but if I missed something, please do point me to it. I have searched Google and this site specifically, but it's always possible I wasn't using the right words.

I am trying to get the column reference based on where the cell matching a specific value is. Using INDEX and MATCH together, I am able to get the row that I want, but I have not been able to find a formula to properly get the column.

"g" is in cell A2 on this sheet.

Current Result:
Error

Expected Result:
Expected

And located at (2,2), or C3, in this sheet.
Table being searched:
enter image description here

Any solution that gets either the true column or the value of the first cell in the column would be helpful.

Spreadsheet

Upvotes: 0

Views: 247

Answers (1)

Maciek
Maciek

Reputation: 131

The problem with the current solution is that HLOOKUP only searches the first row.
The formula to solve this problem will not be so simple.

Here you can find very similar problem.

I took formula from there and changed it a little.

To get the column number you need this:

 ={
  INDEX(FILTER(
    table!$A:$F,
    ARRAYFORMULA(SEARCH("h", QUERY(table!$A:$F,,99^99))
    )),1,1)
}

To get the row number use this:

={
 INDEX(FILTER(
   table!$A:$F,
   ARRAYFORMULA(SEARCH("h", QUERY(TRANSPOSE(table!$A:$F),,99^99))
   )),1,1)
}

Change "h" to a different value or cell reference if needed.

Upvotes: 2

Related Questions