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.

sddv

In sheet2, you just want to enter the ID in a cell and details should be displayed to you.

ry

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.

et7

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

How to use the COUNTIF function in Excel

How to use the SUMIF Function in Excel

Comments

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

  3. escorts18.shutterfly.com

    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!

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

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

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

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

    thanks!
    Karthik

    • 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

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

Leave a Reply to Ervin Gonzalez Cancel reply

Your email address will not be published. Required fields are marked *

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.