When you are burdened with the task of finding matching values in an Excel sheet, what do you do? Do you look into each cell to keep an eye out for duplicates?
If you have done that, you’ve just wasted a whole lot of time.
There’s a way to make things easier, and we’ll show you how. You will get impeccable results and it won’t take nearly as long.
Below are three functions that you can use to find matching values in Excel, namely: MATCH, EXACT, and VLOOKUP functions.
What is the MATCH Function?
The MATCH function is listed under Excel Lookup and Reference functions. It searches a value in the array and returns the position of the value inside the array. For example, if you try to match the value 5 in the range A1:A4, which includes the values 1,5,3,8, the function returns 2, since 5 is the second item in the range.
In financial analysis, the MATCH function and other functions can be used to look up and return the sum of values in a column. It is widely used along with the INDEX function. Learn how to merge INDEX MATCH as a strong search-up combo.
Formula:
=MATCH(lookup_value, lookup_array, [match_type])
The MATCH formula uses the following arguments:
- Lookup_value (required argument) – This is the value that we want to look up.
- Lookup_array (required argument) – The data array that is to be searched.
- Match_type (optional argument) – It can be set to 1, 0, or -1 to return results
Things to remember:
- When matching text values, the MATCH feature does not differentiate between uppercase and lowercase characters.
- N/A! error – Happens if the match function fails to locate the match for the lookup value.
- The function supports approximate and exact matching and wildcards (* or ?) for partial matching.
What is the EXACT Function?
The EXACT function is classified under the Excel TEXT functions. The function helps to evaluate whether two or more text strings or values are precisely the same and if so, returns TRUE. Otherwise, it’s going to return FALSE. The function EXACT is case-sensitive.
The role of the EXACT function is to go along the rows of two columns and locate the matching values in the Excel cells. EXACT means exact. It’s not going to see Los Angeles and los angeles as a match.
Formula:
=EXACT(text1, text2, …)
The EXACT formula uses the following arguments:
- Text1 (required argument) – This is the first string that we want to join. It can be two text strings, numbers, or logical value.
- Text2 (required argument) – This is the second text string.
The EXACT function returns TRUE if the two strings are the same. The EXACT function returns FALSE if the two strings are different.
What is the VLOOKUP Function?
The VLOOKUP feature in Excel is a method to search for a piece of information in a table or data set and to retrieve some of the relevant data/information. Simply put, the VLOOKUP function says the following to Excel: “Look for this piece of information (e.g. apples) in this data set (table) and tell me some important information about it (e.g. the cost of apples).”
Formula:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The VLOOKUP function uses the following arguments:
- Lookup_value (required argument) – Lookup_value specifies the value that we want to look up in the first column of a table.
- Table_array (required argument) – The table array is the data array that is to be searched. The VLOOKUP function searches in the left-most column of this array.
- Col_index_num (required argument) – This is an integer, specifying the column number of the supplied table_array, that you want to return a value from.
- Range_lookup (optional argument) – This defines what this function should return if it does not find an exact match to the lookup_value. The argument can be set to TRUE or FALSE, which means:
- TRUE – Approximate match, that is, if an exact match is not found, use the closest match below the lookup_value.
- FALSE – Exact match, that is, if an exact match is not found, then it will return an error.
Things to remember:
- When a range lookup is removed, the VLOOKUP function will allow a non-exact match but will use an exact match if one exists.
- The greatest weakness of the function is that it always looks right. The details will be taken from the columns to the right of the first column in the table.
- If the search column includes duplicate values, VLOOKUP matches only the first value.
- The function is not case sensitive.
- Suppose there is an established VLOOKUP formula in the worksheet. In that case, formulas can break if a column is inserted in the table. This is because hard-coded column index values do not change immediately as columns are added or erased.
- VLOOKUP encourages the use of wildcards, e.g. an asterisk (*) or a question mark (?).
- If we are working with the function in the table that has numbers entered as text, the following scenario happens: we are simply retrieving numbers as text from a column in a table, it doesn’t matter. But if the first column of the table contains numbers entered as text, we will get an #N/A! error if the lookup value is not also in text form.
- #REF! error – Occurs if either:
- The col_index_num argument is greater than the number of columns in the supplied table_array; or
- The formula attempted to reference cells that do not exist.
- #VALUE! error – Occurs if either:
- The col_index_num argument is less than 1 or is not recognized as a numeric value; or
- The range_lookup argument is not recognized as one of the logical values TRUE or FALSE.
What Else Can You Do With These Functions?
If you’ve done enough time familiarizing these functions and matching values, you can start doing a lot of different things with them. This includes using the MATCH and INDEX functions together to perform something similar to VLOOKUP.