In Google Sheets how do I search for a specific value in a column?

Web Applications Asked by bob.dobbs on December 26, 2020

I’ve started working with Google Sheets.

I’m looking for a value that I believe exists in a specific column.
I know which column the value exists in.

How do I find the value?

One Answer

You want to search for a value in a column range, and return the location of the cell. I couldn't find an appropriate precedent in webapps, so this answer is adapted from Google Spreadsheet Check From What Cell VLOOKUP() Returns. The difference in this case is that the search range does not begin with column A and/or Row#1.

Use this formula: =IFNA(ADDRESS(MATCH($B$1,$D$10:$D$16,0)+$B$6-1,$B$5,1),"No match")

  • "$B$1": value to search
  • "$D$10:$D$16": range to search
  • "$B$6": first row of data
  • "$B$5": column number being searched.




  • MATCH returns the row of the searched value in the search range. Since the search range doesn't necessary begin in row#1, you need to add the actual row on which the search range begins and then subtract one to derive the row on which the searched value exists.
  • ADDRESS(row, column, abs) returns the cell address. In this case, the row is derived from "MATCH", and the column is entered manually. abs indicated whether the format should be "A1" or "R1C1", the formula opts for "A1".

Correct answer by Tedinoz on December 26, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP