Alok Paul
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
4 Methods to Find a Value in Column in Excel
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.
Step 4:
- 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 5:
- Finally, we will find the value colored on the column we selected.
Read More: How to Find Highest Value in Excel Column (4 Methods)
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: Lookup Value in Column and Return Value of Another Column in Excel
Similar Readings
- How to Find First Occurrence of a Value in a Column in Excel (5 Ways)
- Find Last Occurrence of a Value in a Column in Excel (5 Methods)
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.
Read More: How to Find Value in Column Using VBA in Excel (4 Ways)
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.
Read More: How to Find Lowest Value in an Excel Column (6 Ways)
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. Explore our site ExcelDemy for more such articles.
