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:

`=LOOKUP(B8,B5:B9,D5:D9)`

The result is **256.**

**Formula Description:**

The syntax of the formula:

`=LOOKUP(lookup_value,lookup_range,[result_range])`

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:

`=VLOOKUP(B7,B5:D9,3,FALSE)`

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:

`=VLOOKUP("AA-03#",B5:D9,3,TRUE)`

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:**

`=VLOOKUP(lookup_value, table_array,col_index_num, [range_lookup])`

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:

`=IFERROR(VLOOKUP(B8,B5:D9,3,FALSE),"Not Found")`

The result is **256.**

**Formula Description:**

`=IFERROR(value,value_if_error)`

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:

`=INDEX(D5:D9,MATCH(B12,B5:B9,0))`

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

**Formula Description:**

The syntax of the nested formula is:

`=INDEX(array,MATCH(lookup_value,lookup_array,[match_type])`

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

`=INDEX(array,row_num,column_num)`

`=MATCH(lookup_value,lookup_array,[match_type])`

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:

`=TEXTJOIN(",",TRUE,IF(B12=B4:B9,D4:D9,""))`

The result is** 256** which matches our requirements.

**Formula Description:**

The syntax of the nested formula is:

`=TEXTJOIN(delimiter,ignore_empty,text1,IF(logical_test,value_if_true,value_if_false))`

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

*are the results to be shown based on the logical test being true or false.*

**value_if_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.