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.
| =VLOOKUP($A2,Sheet1!$A$2:$D$10,COLUMN(A1)+1,0) |
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.
Explanation:
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 info@exceltip.com
Related Articles:
How to Lookup Entire Row Using Index Match
How to Lookup Entire Column in Excel Using Index Match
How to Retrieve Latest Price in Excel
Retrieving the First Value in a List that is Greater / Smaller than a Specified Value
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity
How to use the VLOOKUP Function in Excel
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.
Can someone help me? I’ve made a phone book that has A-Z sheet in each sheet is the people last name adresss and phone number, I want to make another sheet where I can look up a street address and it will show me all the people living in a certain street.
Hi sir,
i am getting an error while i applying formula on my data. it showing REF# error. can you please help on this error please
let me see the formula.
How to get all the rows when original data contains repetitive rows
HI, I am working with asset data. I have a machine generated file for the first sheet that I am matching to data file I have downloaded into Excel as a second sheet. I have tried to follow and don't understand what I am doing wrong. Syntax I have is: {=VLOOKUP(A2,'Sheet 2'!$A$2:$M$16986,{1,2,3,4,5,6},FALSE) and the only thing it returns is the value of A2. Can you tell what the problem is because I can't. Thank you.
Very useful
Very useful as per my requirement.
excellent submit, very informative. I ponder why the other specialists of this sector do not
notice this. You must proceed your writing. I am sure, you've a huge readers' base already!
Pretty section of content. I just stumbled upon your site and in accession capital to assert that I acquire actually enjoyed account your blog posts.
Any way I will be subscribing to your feeds and even I achievement you access consistently rapidly.
Would it be possible to use this when multiple cells in the array are equal to the look up value?
For the example you provided this would be if two individuals have the same ID # they would display both?
What if there is multiple instances of ID "102" but differing name and genders, how do i get excel to discount anything it's already found?
Hi, please advice on how to change the formulae for columns having same values (102 in the above illustration).
thanks!
Karthik
can we tell how we should use the above formula.i didnt understood
Hi Rupa,
Just download the example file and evaluate function into steps. You would get an idea that how function is working.
Let us know if you still see problem in that.
Cheers,
Team Excel Tip
Is there a VLOOKUP function where I can refer to the first column, pick all "EVT" and copy those rows to another spreadsheet?
Hi Roysius,
Yes, You can retrieve selected values instead of whole column.You can put this formula.
=VLOOKUP(B14,A6:D11,1,0)
Imran.
Is there a formula whereby I can retrieve selected values instead of a whole column?