How to Find a Column Value in Excel – Excel Search Column for Value

Excel helps in analyzing small or large data files containing multiple values. Sometimes, we have to find a specific value from a column in Excel, but it becomes tricky because manually we cannot handle the larger workbooks. It definitely takes time and needs more effort to be able to Excel search columns for value one by one.

For this, we always tend to find some easy approach that can help us in performing such tasks. Today, we will talk about how to find a value in a column in Excel using multiple methods.

Below we have a dataset containing entries of some customers in a Store with the Customer ID, shopping Amount on a specific date, and the Total amount of their shopping done previously.

3 Methods of Excel Search Column for Value

Find Value in a Column in Excel using Conditional Formatting Feature  

Conditional Formatting lets you find any value in Excel. This method will explain how to find value in a specific column in your spreadsheet.

1. Choose the column that has a specific value to be found.
2. Select Cells C5 to C8 in Column C.

3. Open the Home tab and select the Conditional Formatting option.
4. Now, click on Highlight Cells Rules from the drop-down menu of Conditional Formatting.
5. Click on the Equal To option.

6. A pop-up will open where you have to enter the value you want to find out.
7. Suppose you want to find a value of 500.
8. Enter this value in the given section.
9. You can choose the highlight color as well.
10. Choose from the menu.

11. In the end, the required value is colored on the selected column.

Find Value in Column Excel using VLOOKUP Function

Using the VLOOKUP function, you can find a value in a column in Excel. For this, follow the steps given below:

1. At first, a new column is created named Result that shows VLOOKUP.

2. Go to Cell E5 and put the VLOOKUP function.
3. From column range D5 to D8, you will find cell D5.
4. In the argument section, put FALSE because it gives the exact result.
5. The formula will be as: =VLOOKUP(C5, D5:D8, 1, FALSE).

6. Press ENTER key.
7. The selected value is there in the selected column.

You can even compare all the values of Column D with Column E. For this, you will have to follow some additional steps given below:

8. Edit the formula and put a Dollar sign for Absolute Reference. The formula would be as:

=VLOOKUP(C5,$D$5:$D$8,1,FALSE)

9. Press ENTER.
10. Drag the Fill Handle icon from Cell E5.
11. Ultimately, you will get the result of comparison Column D values on Column E.

You will notice the values from cells D5 and D7 are there in Column E. When using the VLOOKUP function, remember that the compared columns should be on the right side of the reference cell, or else the function is of no use.

Find value in Column in Excel using INDEX with MATCH Function

This method uses two functions mutually. The combo of INDEX and MATCH functions is mind-blowing because both of these functions can provide the required results. Let’s find out how this combo works.

Below you can see how the MATCH function works.

1. Go to Cell E5 and make changes in the formula bar.
2. Put the INDEX function and choose a cell collection where you need to find the value.
3. After editing the formula will be as:

=INDEX(D5:D8,MATCH(C5,$D$5:$d$8, 0))

4. Press the ENTER key and the results will be there.
5. You will need to Autofill all the other cells in Column E if you need to display the results in the rest of the cells.
6. Edit the formula and apply Absolute Reference before entering the above option.

The formula will be as: =INDEX($D$5:$D$8,MATCH(C5,$D$5:$D$8,0))

7. Drag the Fill Handle icon to the last cell in Column E and the result will be there.

Final Thoughts

This post explains how to search for a value in a column in Excel by using different methods. You are now aware of how to use Conditional Formatting, VLOOKUP, MATCH, and INDEX functions to perform Excel find value in column function. Each method is vital and effective that’s why you must try them at least once.