Find a Number in a Column / Workbook – Excel & Google Sheets

This tutorial will demonstrate how to find a number in a column or workbook in Excel and Google Sheets.

find number in column Main Function

Find a Number in a Column

The MATCH Function is useful when you want to find a number in a array of values.

This example will find 500 in column B.

Match Position in Array

The position of 500 is the 3rd value in the range and returns 3.

Find Row Number of Value Using ROW, INDEX and MATCH Functions

To find the row number of the value found, we can add the ROW and INDEX functions to the MATCH Function.

Find Row Using INDEX and MATCH

AutoMacro - VBA Code Generator

ROW and INDEX Functions

The MATCH Function returns 3 as we have seen from the previous example.  The INDEX Function will then return the cell reference or value of the array position returned by the MATCH Function.  The ROW function will then return the row of that cell reference.

Filter in Excel

We can find a specific number in an array of data by using  a Filter in Excel.

Using Filter

 

  1. Click in the Range you want to filter ex B3:B10.
  2. In the Ribbon, select Home > Editing > Sort & Filter > Filter.

Using Filter 2

  1. A drop-down list will be added to the first row in your selected range.

Using Filter 3

  1. Click on the drop-down list to get a list of available options.

Using Filter 4

  1. Type 500 in the search bar.

Using Filter 5

  1. Click OK.

Using Filter

  1. If your range has more than one value of 500, then both would show.

Using Filter 6

  1. The rows that do not contain the required values are hidden, and the visible row headers are shown in BLUE.
  2. To clear the filter, click on the drop-down and then click Clear Filter From “Values”.

Using Filter 7

To remove the filter entirely from the Range, select Home > Editing > Sort & Filter > Clear in the Ribbon.

Using Filter 8

Find Number in Workbook or Worksheet using FIND in Excel

We can look for values in Excel using the FIND functionality.

  1. In your workbook, press Ctrl+F on the keyboard.

OR

In the Ribbon, select Home > Editing > Find & Select.

Using Find 01

  1. Type in the value you wish to find.

Using Find 02

 

  1. Click Find Next.

Using Find 03

  1. Continue to click Find Next to move through all the available values in the Worksheet.
  2. Click Find All to find all the instances of the value in the worksheet.

Using Find 04

Click Options.

Using Find 05

  1. Amend the Within from Sheet to Workbook and click Find All

Using Find 06

  1. All the matching values will appear in the results shown.

 

Find a Number – MATCH Function in Google Sheets

The MATCH Function works the same in Google Sheets as it does in Excel.

find number in column Google Function

Find Row Number of Value Using ROW, INDEX and MATCH Functions in Google Sheets

The ROW, INDEX and MATCH Functions works the same in Google Sheets as it does in Excel.

Google 2

Filter in Google Sheets

We can find a specific number in an array of data by using Filter in Google Sheets.

Google Find 01

  1. Click in the Range you want to filter ex B3:B10.
  2. In the Menu, select Data > Create a filter

Google Find 02

  1. In the Search bar, type in the value you wish to filter on.

Google Find 03

  1. Click OK.

Google Find 04

  1. If your range has more than one value of 500, then both would show.
  2. The rows that do not contain the required values are hidden.
  3. To clear the filter, click on the drop-down, click Select All” and then click

Google Find 05

To remove the filter entire, in the Menu, select Data > Turn off Filter.

Google Find 06

 

 

Find Number in Workbook or Worksheet using FIND in Google Sheets

We can look for values in Google Sheets using the FIND functionality.

  1. In your workbook, press Ctrl+F on the keyboard.

Google Find 07

  1. Type in the value you wish to find.
  2. All the matching values in the current sheet will be highlighted, with the first one being selected as shown below.

Google Find 08

  1. Select More Options.

Google Find 09

  1. Amend the search to All Sheets.

Google Find 10

  1. Click Find.
  2. The first instance of the value will be found. Click Find again to go to the next value and continue to click Find to find all the matching values in the workbook.
  3. Click Done to exit out of the Find and Replace Dialog Box.

 

Excel Practice Worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!