The basic idea is to use a lookup function to compare two phone numbers and return a value from another column if there is a match. A lookup function takes a value and searches for it in a range of cells, then returns a corresponding value from another column or row. There are different lookup functions in Excel, such as VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP, etc. Each function has its own syntax and advantages, but they all follow the same logic.
Procedures
To check for matches between two phone numbers and if matched display the result from a different column in excel formula, you need to follow these steps:
- Identify the range of cells that contains the phone numbers and the result values. For example, if you have a table of customer data with phone numbers in column A and names in column B, the range would be A2:B10.
- Identify the cell that contains the phone number you want to look up. For example, if you want to look up the name of the customer who has the phone number 555-1234, the cell would be C2.
- Choose a lookup function that suits your needs. For example, if you want to find an exact match, you can use VLOOKUP or XLOOKUP. If you want to find a partial match, you can use VLOOKUP with wildcards or XLOOKUP with a custom match mode.
- Enter the lookup function in the cell where you want to display the result. For example, if you want to display the name of the customer who has the phone number 555-1234 in cell D2, you can enter the following formula: =VLOOKUP(C2,A2:B10,2,FALSE)
- Adjust the arguments of the lookup function according to your data. For example, in the VLOOKUP formula above, C2 is the lookup value, A2:B10 is the lookup array, 2 is the column index number, and FALSE is the match type. You can change these arguments as needed.
- Copy and paste the formula to other cells if you want to look up more phone numbers and display more results.
Explanation
To illustrate the steps above, let’s use a scenario to give a detailed example with real numbers. Suppose you have a table of customer data like this:
| Phone Number | Name | |
|---|---|---|
| 555-1111 | Alice | alice@example.com |
| 555-2222 | Bob | bob@example.com |
| 555-3333 | Charlie | charlie@example.com |
| 555-4444 | David | david@example.com |
| 555-5555 | Eve | eve@example.com |
You want to check if the phone numbers in column E match any of the phone numbers in column A, and if so, display the name of the customer in column F. The phone numbers in column E are:
| Phone Number |
|---|
| 555-1234 |
| 555-2222 |
| 555-6789 |
| 555-4444 |
| 555-5555 |
To do this, you can use the VLOOKUP function in column F. The VLOOKUP function has the following syntax:
=VLOOKUP(lookup_value, lookup_array, col_index_num, [range_lookup])
The lookup_value is the value you want to find in the first column of the lookup_array. The lookup_array is the range of cells that contains the data you want to search. The col_index_num is the column number in the lookup_array that contains the value you want to return. The range_lookup is an optional argument that specifies whether you want an exact match (FALSE) or an approximate match (TRUE).
In this case, the lookup_value is the phone number in column E, the lookup_array is the range A2:B6, the col_index_num is 2, and the range_lookup is FALSE. So, the formula in cell F2 is:
=VLOOKUP(E2,A2:B6,2,FALSE)
This formula will look for the value in cell E2 (555-1234) in the first column of the range A2:B6. If it finds an exact match, it will return the value in the same row from the second column of the range A2:B6. If it does not find an exact match, it will return an error value (#N/A).
In this case, there is no exact match for 555-1234 in column A, so the formula returns #N/A in cell F2.
To fill the rest of the cells in column F, you can copy and paste the formula, or drag the fill handle down. The results are:
| Phone Number | Name |
|---|---|
| 555-1234 | #N/A |
| 555-2222 | Bob |
| 555-6789 | #N/A |
| 555-4444 | David |
| 555-5555 | Eve |
As you can see, the formula returns the name of the customer if there is a match, and #N/A if there is no match.
Other approaches
There are other ways to check for matches between two phone numbers and if matched display the result from a different column in excel formula. Here are some alternatives:
- You can use the XLOOKUP function instead of the VLOOKUP function. The XLOOKUP function has the following syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The lookup_value, lookup_array, and return_array arguments are similar to the VLOOKUP function, except that the lookup_array and return_array can be in any order and any size. The if_not_found argument is an optional argument that specifies what to return if the lookup_value is not found. The match_mode argument is an optional argument that specifies how to match the lookup_value. The search_mode argument is an optional argument that specifies whether to search from the first or last value, and whether to search by binary or linear search.
In this case, the lookup_value is the phone number in column E, the lookup_array is the range A2:A6, the return_array is the range B2:B6, the if_not_found argument is omitted, the match_mode argument is 0 (exact match), and the search_mode argument is omitted. So, the formula in cell F2 is:
=XLOOKUP(E2,A2:A6,B2:B6,0)
This formula will look for the value in cell E2 (555-1234) in the range A2:A6. If it finds an exact match, it will return the value in the same position from the range B2:B6. If it does not find an exact match, it will return an error value (#N/A).
The advantage of using the XLOOKUP function is that it is more flexible and powerful than the VLOOKUP function. You can use different match modes, such as wildcard match, case-sensitive match, or custom match. You can also specify what to return if the lookup value is not found, such as a blank cell, a default value, or a formula. You can also search from the last value or use a binary search for faster performance.
- You can use the INDEX and MATCH functions together instead of the VLOOKUP function. The INDEX function has the following syntax:
=INDEX(array, row_num, [column_num])
The array argument is the range of cells that contains the values you want to return. The row_num argument is the row number in the array that contains the value you want to return. The column_num argument is an optional argument that specifies the column number in the array that contains the value you want to return.
The MATCH function has the following syntax:
=MATCH(lookup_value, lookup_array, [match_type])
The lookup_value, lookup_array, and match_type arguments are the same as the VLOOKUP function.
In this case, the array argument is the range B2:B6, the row_num argument is the result of the MATCH function, and the column_num argument is omitted. The lookup_value is the phone number in column E, the lookup_array is the range A2:A6, and the match_type argument is 0 (exact match). So, the formula in cell F2 is:
=INDEX(B2:B6,MATCH(E2,A2:A6,0))
This formula will look for the value in cell E2 (555-1234) in the range A2:A6. If it finds an exact match, it will return the row number of the match. Then, the INDEX function will return the value in the same row from the range B2:B6. If it does not find an exact match, the MATCH function will return an error value (#N/A), which will cause the INDEX function to return an error value (#N/A) as well.
The advantage of using the INDEX and MATCH functions together is that they are more versatile and flexible than the VLOOKUP function. You can use them to look up values in any column, not just the first one. You can also use them to look up values in rows, columns, or two-dimensional ranges. You can also use different match types, such as approximate match or wildcard match.
