Lookup Value in Column and Return Value of Another Column in Excel

The corporate world works with huge datasets. Microsoft Excel has made their work easier through various formulas to maintain the datasets with ease. However, lookup for values in a column and getting the return of another column of the relatable data is the most common thing in maintaining datasets. The article will explain five formula-based methods to lookup a value in one column and return the value of another column in Excel.

Download Practice Workbook

You can download the Workbook for practice from the link below.

5 Methods for Lookup Value in Column and Return Value of Another Column in Excel

We will use the following dataset to explain five different formula-based methods to lookup a value in a column and return the value of another column in Excel.

The dataset contains data related to the Sales_ID, the Date of Sales according to the ID number, and the Number of Sales that occurred for that particular ID number. Let us see the five methods to extract data from a column and get the result of a different column.

1. Using LOOKUP Formula to Lookup Value in Column and Return Value of Another Column

The topic itself features the formula name of this method. This is the shortest formula among all others explained below.

Assume that you want to search for the Sales_ID “AT-02#” and get the result of the number of sales that occurred for that particular sale ID.

The formula for this will be:

The result is 256.

Formula Description:

The syntax of the formula:

Here,

lookup_value indicates the value which we are looking for. For this dataset, it is AT-02#.

lookup_range is the column range where we will look for the value. In this case, it is the range of the column named Sales_ID.

result_range is the column from where the result has to be taken by matching with the lookup value. It is 256 according to the dataset.

2. Using the VLOOKUP Formula to Lookup Value in Column and Return Value of Another Column

Furthermore, there is another similar type of formula formed using the VLOOKUP function.

There are two types of VLOOKUP formulas. One gives the exact match of the lookup value and the other gives a partial match.

Exact Match:

Let us first see the exact match result. Now, let us say we want to look for the Sales_ID AB-04# and get the result of the number of sales that occurred for this ID.

The formula for an exact match will be:

The result is 548.

Partial Match:

Let us assume that we want to search for “AA-03#” and get the result of the number of sales for this ID. Since the ID is not present in the dataset, the result will extract the partial match according to the formula.

The formula for the partial match:

The result shows 600. It cannot find the exact match of “AA-03#”. Hence it is showing the extract the result of the value close enough to the looked-up value

Formula Description:

Here, 

lookup_value is the value we are looking for that is AA-03#.

table_array is the range of the dataset

col_index_num is the column index number of the column from where we want to extract the result

range_lookup is whether the result will be an exact or partial match. You will notice the above-mentioned types of VLOOKUP formula differ only in this parameter taking FALSE for the exact match and TRUE for the partial one.

3. Using VLOOKUP and IFERROR to Lookup Value in Column and Return Value of Another Column

However, you can use another formula using VLOOKUP nested with IFERROR to look up the value in a column and return the value of another column as result.

Suppose you want to search for AT-02# and get the result of the number of sales for this ID.

The formula for the given dataset will be:

The result is 256.

Formula Description:

Here,

The IFERROR function takes two arguments value and value_if_error. 

value takes the VLOOKUP formula mentioned in method 2.

value_if_error is the result that will show when the looked-up value cannot be found in the range.

4. Combining INDEX and MATCH to Lookup Value in Column and Return Value of Another Column

Apart from that, you can use nested INDEX and MATCH formulas to lookup for a value in a column and get the result of another column in the dataset.

Suppose you have Sales_ID AT-02# and you want to get the number of sales for this ID.

The formula for this dataset is as follows:

The result shows the Number of Sales as 256 for the Sales_ID AT-02#.

Formula Description:

The syntax of the nested formula is:

If we see the individual formula it will look like this:

Here, the nested formula includes the INDEX and MATCH functions.

The INDEX function takes an array as a reference to extract the result.

The rest of the arguments are the row_num and the column_num of the result to be extracted.

The MATCH function takes the lookup_value where the searching value is taken.

Other than that, it takes a lookup array which is the range of the column where value has to be searched.

Finally, it takes the match_type which is 0 for an exact match, and 1 and -1 for less than or greater respectively than the searched value result of the other column. You can use any of them as per requirement. For this dataset, we have used the exact match type.

5. Merging TEXTJOIN and IF to Lookup Value in Column and Return Value of Another Column

Lastly, we can also combine the TEXTJOIN and IF functions to lookup for a value in a column and get the result of value from another column.

Let’s say we have the Sales_ID AT-02# again and we want to find the result of the Number of Sales for this Sales_ID using nested TEXTJOIN and IF formula.

The formula for this:

The result is 256 which matches our requirements.

Formula Description:

The syntax of the nested formula is:

Here,

The TEXTJOIN function takes delimiter to limit the text joining

ignore_empty contains the value to be ignored if found empty

text1 and rest are texts which can be added to get the texts.

The IF function takes the logical argument on basis of which true or false will be determined

value_if_true and value_if_false are the results to be shown based on the logical test being true or false.

Things to Remember

You have to put the values according to the formula syntax to get the proper results.

Conclusion

The article explains five different methods that include functions: LOOKUP, VLOOKUP, MATCH, INDEX, TEXTJOIN, etc. Eventually, the methods are based on formulas either nested or individual to lookup value in a column and return the value of another column in Excel. I hope this article has helped you solve your problem. For relatable articles, you can see the Related Readings section. If you have any further queries you can ask in the comment section.

Related Readings

• How to Find Value in Column in Excel (4 Methods)
• Find Value in Column Using VBA in Excel (4 Ways)