Check If a Value Exists in a Range
This post will guide you how to use VLOOKUP function to check if a value exists in a given range of cells in Excel. How to check if a specified value exists in a range and then return the value in the adjacent cell.
For example, you want to look up the text value “Excel” in the range B1:C7, and you found it in the Cell B4, then return the adjacent Sales value (C4) in the column C.
| Name | Sales |
| access | 45 |
| word | 66 |
| Excel | 34 |
| ppt | 23 |
| Word | 435 |
| Word | 443 |
Check If a Value Exists in a Range
Let’s write down the following Excel Formula based on the VLOOKUP function:
=VLOOKUP("Excel", B1:C7,2,TRUE)
Type this formula into the formula box in cell E1, then press Enter.
Let’s see how this formula works:
The VLOOKUP function can be used to check if a given values exists in a range of cell, then return the value in a specified column that is specified by the third argument in the function. So number 2 is the column number that we want to pick. The “Excel” is the value for that we want to lookup. B1:B7 is a range from which we want to lookup the value. The TRUE value indicates that we want to lookup an approximate match from range B1:C7.
Related Formulas
- Lookup Entire Row using INDEX/MATCH
If you want to lookup entire row and then return all values of the matched row, you can use a combination of the INDEX function and the MATCH function to create a new excel array formula. - Extract the Entire Column of a Matched Value
If you want to lookup value in a range and then retrieve the entire row, you can use a combination of the INDEX function and the MATCH function to create a new excel formula..… - Lookup the Next Largest Value
If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula..
Related Functions
- Excel VLOOKUP function
The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
