There are a lot of times when working in Excel we’ll need to pull information from another spreadsheet. You can easily do that by utilizing the VLOOKUP function or Index and Match function combo. In this article, we will explore how to use the VLOOKUP and Index and Match function combo in Excel to get data from another sheet based on cell value.
Consider the following scenario: you have a range of data in Sheet 1, with each column header representing a date, and you want to transfer the entire column from Sheet 1 to Sheet 3 depending on the date in cell A1 of Sheet 2. So, how can you do that?
You can easily accomplish the job by using the VLOOKUP or Index and Match function. So, keep reading this post until the end to know how to do that.
Get Data from Another Sheet Using VLOOKUP/HLOOKUP function.
First, we are going to use the VLOOKUP function. It is a pretty basic way to pull data from one sheet to another. Now, before moving on to the actual tutorial, you should know the syntax of the Vlookup formula. the following is the general formula for getting data from another sheet:
=VLOOKUP(lookup_value, ’sheet_name’!range, col_index_num, range_lookup)
The explanations of the parameters are given below:
- lookup_value: It is a value that we wish to search up in another sheet.
- ’sheet_name’!range: It is a range in the other sheet where we wish to look up.
- col_index_num: It is the number of a column in the other sheet from which we would want to get a value.
- range_lookup: range lookup has a default value of 0. This signifies that we want to get the same match for a lookup value.
Now that you understand the parameters, it’s time to make a spreadsheet. For your convenience, we created a demo sheet in Excel. In the below image, you can see Sheet 1, where we are going to pull data from Sheet 2.
In Sheet 1, we have the headings City, State, and Employee. But the State column is empty, and we are going to fill it based on cell values on Sheet 2.
Now follow the below steps to get data from another sheet based on cell values:
- Open this demo spreadsheet and select C3
- Enter the formula given below:
=VLOOKUP(B3,Sheet2!B3:C7,2,FALSE)
- Once you enter the formula, click enter, and the State value from Sheet 2 will appear.
- Now click on the C3 cell again and hold the Plus icon, which is located on the right bottom corner, and drag it down all the way to the C7
- All the cells from C3 to C7 will now be filled based on the State column from Sheet 2.
Explanation: When you will type the formula in the lookup value, you will need to select the B3 cell as we will need the State value on the City. Now, in the table array, click Sheet 2 and select from B3 to C7. Now in the col index num value, type 2 as the State value is in the number 2 column. In range lookup, type 0 or False as we need it exactly as it is.
Get Data From Another Sheet Using Index and Match function combo.
Now we are going to show you the Index and Match function combo. Personally, I prefer using this Index and Match function combo. And there are so many reasons for that. For example,
- Index Match function can be used in both ways (right to the left and left to right), whereas Vlookup cannot look to the left of the sheet in either direction.
- The Vlookup function will break or produce incorrect results if you try to insert or remove columns from the table or sheet from which you are obtaining data, but this does not occur with the Index Match function.
- If you are dealing with large amounts of data, such as thousands of rows, you will need to make thousands of Vlookup formulas. In this case, Excel will perform far better if you utilize IndexMatch instead of Vlookup.
Whatever, enough chitchat! Now let’s see how you can get data from another sheet based on cell value by using the Index and Match function combo.
- First, open the spreadsheet and select cell C3.
- Enter the formula given below:
=INDEX(Sheet2!B3:C3,MATCH(Sheet1!C11,Sheet2!B2:C2,0))
- Once you enter the formula, click enter, and the State value from Sheet 2 will appear.
- Now click on the C3 cell again and hold the Plus icon, which is located on the right bottom corner, and drag it down all the way to the C7
- All the cells from C3 to C7 will now be filled based on the State column from Sheet 2.
Explanation: Let’s have a look at how this formula really works. First, let’s have a look at the formula below:
=INDEX(Sheet2!B3:C3,MATCH(Sheet1!C11,Sheet2!B2:C2,0))
In the first part, we have to call the Index formula; then, in the array field, we will need to select the cell from B3 to C3 in Sheet 2 because we will pull data from there. Next, call the Match function and select the State field, which is C2. Because we will get data based on this State field, now, select from cells B2 to C2 in Sheet 2. Next, type 0 or select False, as we need the data exactly how it is.
Frequently Asked Questions (FAQs)
How can I get data from another sheet in Excel based on a cell value?
You can easily get data from another sheet by using the Vlookup function. Also, if you are an experienced Excel user, you can use the Index and Match function combo to do the job.
What is an Xlookup in Excel?
Xlookup is a function that is an enhanced version of the VLOOKUP function. It has the ability to function in either direction and gives exact results by default, making it simpler and more comfortable to use compared to its predecessor, which is a significant improvement.
Is INDEX Match function faster than Xlookup?
Yes, the Index Match combo function is much faster compared to Xlookup. According to many tests, approximately it is 30% faster than a 2D XLOOKUP function.
Conclusion
Although this is a simple brief article, there are several lessons to be learned from it. By following this article, you will know how to get data from another sheet based on cell values in Excel. If you are a Data Entry operator, you can impress your boss by using one of the methods above to make the sheet professional. We hope this article has helped you to know something.
