Search entire worksheet for value

How to Search an Entire Worksheet for a Value

Alex has a WORKSHEET that is wrought with several NAMES OR VALUES. He needs to know how many times the NAME of his friend, PETER, appears on the WORKSHEET. How will he do it?

To SEARCH ENTIRE WORKSHEET FOR A VALUE, the COUNTIF function can be used. It counts the number of times the VALUE appears and returns the VALUE as an integer.

Search entire worksheet for value

Formula

In the example below, the formula in Cell C5 is:

=COUNTIF(Sheet2!1:1048576, C4)

The syntax for this formula is:

=COUNTIF(range, criteria)

Explanation

The value in Cell C4 is searched for in the entire RANGE (SHEET 2) and the COUNTIF function displays the result numerically for all Cells that fulfills the CRITERIA.

Example

STEP 1

  • Open an EXCEL WORKSHEET
  • Type the data into SHEET 1 and SHEET 2. On SHEET 1, we are searching for the number of times the name PETER appears. Open SHEET 2 at the bottom (CHECK FIGURE 1) of the WORKSHEET and type the data as shown

Figure 1- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE

Figure 2- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE

STEP 2

  • Click on Cell C5 on SHEET 1
  • Type COUNTIF( into the cell as shown in figure 3. To get the RANGE (the cells containing several values including the ones we are interested in), we need to select the cells on WORKSHEET 2. We could as well select all cells to make things easier. To do this, click on SHEET 2. You will see the content of the fx bar as shown in figure 4.

Figure 3- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE

Figure 4- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE

STEP 3

  • Beside Column A, just above ROW 1 on the figure above, you will see an arrow
  • Click on this arrow. This SELECTS the entire WORKSHEET and the fx bar displays what you have in figure 5

Figure 5- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE

 

STEP 4

  • Type a comma on the fx bar as shown in the figure below. Leave a space. Type C4 (WE ARE STILL INTERESTED IN PETER!)

Figure 6- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE

  • Now, click the ENTER button

Figure 7- HOW TO SEARCH ENTIRE WORKSHEET FOR VALUE

Alex has solved his problem in one-piece. The same approach works no matter the size of the data. Ensure you take the precaution of adhering to the above steps religiously to prevent working on this fruitlessly.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar