# How to Retrieve The Entire Row of a Matched Value

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.

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

How to use the COUNTIF function in Excel

How to use the SUMIF Function in Excel

1. Ervin Gonzalez

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.

2. 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

3. How to get all the rows when original data contains repetitive rows

4. 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.

5. Very useful

6. Very useful as per my requirement.

7. escorts18.shutterfly.com

excellent submit, very informative. I ponder why the other specialists of this sector do not

8. Escorts in Chandigarh

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.

9. 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?

10. 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?

11. Hi, please advice on how to change the formulae for columns having same values (102 in the above illustration).

thanks!
Karthik

12. 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

13. Is there a VLOOKUP function where I can refer to the first column, pick all "EVT" and copy those rows to another spreadsheet?

14. Hi Roysius,

Yes, You can retrieve selected values instead of whole column.You can put this formula.

=VLOOKUP(B14,A6:D11,1,0)

Imran.

Terms and Conditions of use

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.