We use Excel in our day-to-day work specially in data calculating and organizing. Sometimes we need to search for value in a specific column. If we do this manually it will be very time-consuming. We can do this by using functions. So today here we are going to discuss how to find value in a column in Excel.

We take a dataset of some customers in a Super shop with the **Customer ID**, shopping **Amount** on a particular date, and also a **Total** if they bought anything previously.

**Table of Contents**Expand

## How to Find Value in Column in Excel: 4 Methods

### 1. Apply Conditional Formatting Feature to Find Value in a Column in Excel

We can find any value in Excel using **Conditional Formatting**. Here we will find value in a particular column in an Excel spreadsheet.

**Step 1:**

- First, select the column where we want to find the value.
- Here we select
**Cells C5**to**C8**in**Column C**.

**Step 2:**

- Go to the
**Home**tab first. - Now select the
**Conditional Formatting.** - Select
**Highlight Cells Rules**from the drop-down of**Conditional Formatting**. - Now select
**Equal To**.

**Step 3:**

- Now we will get a
**Pop-Up**. - Here we need to put the value we want to find out.
- We want to find a value which is
**500**. - Put this value on the
**marked 1**section on the following image. - We can also choose the highlight color.
- Select this from the menu as in the following image.

**Step 4:**

- Finally, we will find the value colored on the column we selected.

### 2. Using Excel VLOOKUP Function to Find Value in a Column

We can find a value in a column in Excel using the** VLOOKUP **function. The process is given below.

**Step 1:**

- First, we create a new column named
**Result**to show the**VLOOKUP.**

**Step 2:**

- Now go to
**Cell E5**and type the**VLOOKUP**function. - Here we will find the
**Cell D5**from the column**range****D5**to**D8**. - We put
**FALSE**in the argument section because we need the exact result. - So, the formula becomes:

`=VLOOKUP(C5, D5:D8,1,FALSE)`

**Step 3:**

- Now, press
**Enter**. - As our selected value is found on the selected column, we will see that on this cell.

We can also compare all the values of **Column D** with **Column E**. Some additional steps are needed for that. The steps are given below.

**Step 4:**

- Edit the formula.
- Put
**Dollar ($)**sign to use**Absolute Reference**. The formula:

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

- Then press
**ENTER**.

**Step 5:**

- Now, pull down the Fill Handle icon from
**Cell****E5**. - Finally, get the result of comparing
**Column D**values on**Column E**.

Here we see that the values from cells **D5** and **D7** are found in **Column E**. And the rest of the values do not exist in **Column E**. So, the function will return **#N/A errors** for them.

**Note:** In the case of **VLOOKUP,** the comparing column must be on the right side of the reference cell. Otherwise, this function will not work.

**Read More:** How to Find Multiple Values in Excel

### 3. Insert MATCH Function to Find Value in a Column in Excel

We can find a value in a column by inserting the** MATCH **function.

**Step 1:**

- In our data set, we already added a column
**Result**to show the different function results. - Now type the formula in
**Cell E5**. - Type the formula as given:

`=MATCH(C5,$D$5:$D$8,0)`

- Here we will find the value of
**Cell C5**in**Column**E of the range**D5 to D8**. - Here we use the absolute sign so that the cell reference does not change.
- The last argument has been used as
**0**, as we want to get the exact result. - Finally, get the result of comparing
**Column D**values on**Column E**.

**Step 2:**

- Then press
**Enter**.

**Step 3:**

- We get
**1**in**Column E**. It means our cell value is in the 1st position of our selected range. - Now, use Fill Handle to autofill the entire column and we will get the positions if the values of
**Column C**are found on**Column E**.

If we want to see **TRUE **or **FALSE **instead of position. We will need to apply the following steps.

**Step 4:**

- We will use the
**ISERROR**function. - This function returns
**TRUE**if the error is found otherwise**FALSE**. - Now apply this function to
**Cell E5.**The formula becomes:

`=ISERROR(MATCH(C5,$D$5:$D$8,0))`

- Use Fill Handle from
**Cell E5**.

**Step 5:**

- We will see
**FALSE**on**Cell E5**. - The reason for showing
**FALSE**has been explained in the previous step. - Now, use
**NOT function**. This function alternates the TRUE and FALSE. - We are using the
**NOT function**so that, we can understand the actual scenario. - The formula finally becomes,

`=NOT(ISERROR(MATCH(C5,$D$5:$D$8,0)))`

This gives a clear idea of which value is found or not at a glance.

### 4. Link INDEX with MATCH Function to Find Value in a Column

Here we link the **INDEX** function with the **MATCH** function. We already know how to apply the **MATCH** function. We will explain after that step.

**Step 1:**

- The following image is output after applying the
**MATCH.**

**Step 2:**

- Now, go to
**Cell E5**and edit the formula bar. - Type
**INDEX**function and select a range where to find the value. - After editing the formula becomes:

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

**Step 3:**

- Now, press
**ENTER.** - The result is shown in the following image.

**Step 4:**

- If we want to show results in the rest of the cells, we have to autofill the rest of the cells in
**Column E**. - Before that, edit the formula and use
**Absolute Reference**. - Now the formula becomes:

`=INDEX($D$5:$D$8,MATCH(C5,$D$5:$D$8,0))`

**Step 5:**

- Then drag down the
**Fill Handle**icon up to the last cell in**Column E**. - And the final output is shown in the following image.

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Conclusion

Finding a value in a column in Excel is discussed in our discussion. Conditional formatting, **VLOOKUP, MATCH,** and** INDEX** are the methods used to solve this problem. We tried to provide the easiest ways. Now, according to need the users will find which method is easier for them to apply.

## Related Articles

- How to Find First Value Greater Than in Excel
- How to Find First Occurrence of a Value in a Range in Excel
- Find Last Value in Column Greater than Zero in Excel
- How to Find First Occurrence of a Value in a Column in Excel
- How to Find Last Occurrence of a Value in a Column in Excel

**<< Go Back to Find Value in Range | Excel Range | Learn Excel**