In this article, we will learn how to retrieve the entire row of a matched value by using the Vlookup function in Microsoft Excel.
How amazing it would be if you could just enter the employee id and walla! Get all the details of that employee. You will have all the data associated with that employee.
Now you can answer questions faster related to that employee.
Data retrieval using Vlookup is easy in Excel. We will use Vlookup and Column() function of excel.
Let’s say you have data arranged in sheet1 like below image.
In sheet2, you just want to enter the ID in a cell and details should be displayed to you.
To do this, use this formula in B2 cell.
Now copy B2 in all the cells. Your output is ready. The details will change when you change ID.
Change ID in an existing cell or just add a new row with a new ID, all the data will be displayed until you have this formula referencing that ID cell.
It's just a simple Vlookup function.
On the place of Col_index_Num, we used COLUMN(A1)+1.
COLUMN function of excel returns column index number of a given cell.
So here I have given the reference of the starting column (A1) of our data table. It will return 1. Since I want to get value from column 2 for the name, I added 1 to COLUMN(A1). If you copy this formula to the next column, it will automatically become COLUMN(B1)+1 and so on.
Tip: You can also give reference to the B2 cell in the COLUMN function. then you don’t need to add 1 to it.
So yeah, it's simply a trick to get the whole row using VLOOKUP and COLUMN function. It just creates a copy of data from one sheet to another if a condition is met. You can modify it to get the whole column's data. You just need to play around a bit. And if you stumble anywhere, let me know in the comments section.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at email@example.com
How to Lookup Entire Row Using Index Match
How to Lookup Entire Column in Excel Using Index Match
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.