+ Reply to Thread
Results 1 to 8 of 8

Vlookup returning mutiple values into multiple rows

  1. #1
    Registered User
    Join Date
    05-07-2010
    Location
    Queensland, Australia
    MS-Off Ver
    Excel 2003
    Posts
    29

    Question Vlookup returning mutiple values into multiple rows

    Hi,
    I'm a first timer to the forum posts so I hope I do this right.

    I currently have 2 worksheets say Sheet1 and Sheet 2
    Sheet 1 contains a large amount of data under 5 headings:
    A Name
    B Item
    C Value
    D Qty
    E Status

    There are multiple rows for each persons name that contains different data under the other headings.
    In the second sheet I am wanting to have these same heading however be able to have a cell say A1 that is a valadation list for names and A2 also a valadation list but for Status . Once this name and status has been selected I want to use a 2 crieria Vlookup function to poulate the table below with all rows containing the person's name and the status.

    I have tried different vlookups, UDFs and macros but can't seem to get what I need.

    Any help is helpful

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup returning mutiple values into multiple rows

    In F2 of the Sheet2, enter

    =IF(AND(A2=Sheet1!$A$1,B2=Sheet1!$A$2),Count(F$1:F1)+1,"")

    and copy down

    In G2 enter: =Max(F:F)

    In Sheet2, anywhere, enter:

    =IF(ROWS($A$1:$A1)>Sheet1!$G$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$F:$F,0)))


    and copy down and across as far as you need.

    adjust ranges and sheetnames as necessary.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    05-07-2010
    Location
    Queensland, Australia
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Vlookup returning mutiple values into multiple rows

    Hi NBVC,

    Thanks for the reply however I'm not sure if I understand the formulas you are writing and if the sheets are matching up correctly.

    Futher information I should have included in my first email:
    Sheet1 headings for the data are located in Row 1, the data starts at Row 2 and down
    Sheet2 A1 has the first criteria (Name)
    Sheet2 A2 has the second criteria (Status)

    I'm not sure where I should input the formulas you suggest above. Can you please step through it for me?

    Many Thanks

  4. #4
    Registered User
    Join Date
    05-07-2010
    Location
    Queensland, Australia
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Vlookup returning mutiple values into multiple rows

    I've also tried using a Small function to gain the row reference but somehow can't get an index function to return the value. However this only uses 1 criteria so far This is the formula so far
    =SMALL(IF('Sheet1!$A$4:$A$385=Sheet2!$A$1,ROW('Sheet1'!$A$4:$E$385)),ROW(1:1))

    when i have entered the index function it turns out like this but only returns a result of 0
    =INDEX('Sheet1!$A$4:$E$385,SMALL(IF('Sheet1!$A$4:$A$385=Sheet2!$A$1,ROW('Sheet1'!$A$4:$E$385)),ROW(1:1)),1)

    Any Help is greatly appreciated

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup returning mutiple values into multiple rows

    Okay, I mixed up your sheet references a bit...

    This formula:

    =IF(AND(A2=Sheet2!$A$1,B2=Sheet2!$A$2),Count(F$1:F1)+1,"")

    should go in F2 of Sheet1 (next to the original database), then copy it down.

    in G2: =MAX(F:F)

    then the rest is the same as I had before:

    In Sheet2, anywhere, enter:

    =IF(ROWS($A$1:$A1)>Sheet1!$G$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$F:$F,0)))


    and copy down and across as far as you need.

    adjust ranges and sheetnames as necessary.

  6. #6
    Registered User
    Join Date
    05-07-2010
    Location
    Queensland, Australia
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: Vlookup returning mutiple values into multiple rows

    Hi NBVC,

    Thanks for the clarification. I'm having a little trouble understanding how these formulas work, can you please explain it to me.

    Many thanks

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup returning mutiple values into multiple rows

    The first formula:

    =IF(AND(A2=Sheet2!$A$1,B2=Sheet2!$A$2),Count(F$1:F1)+1,"")

    is simply a counter.

    It checks if A2 and B2 are equal to A1 and A2 on Shee2, respectively. If it does, then it counts how many numbers are above the formula and adds 1 to it, so that it can create a count.. you will only have numbering where the conditions are true.

    the =MAX(F:F) formula just finds the max (or last) number. This is used in the next formula to determine when it has finished fetching all the relevant data and start returning blanks instead of errors.

    The main formula:



    =IF(ROWS($A$1:$A1)>Sheet1!$G$2,"",INDEX(Sheet1!A:A,MATCH(ROWS($A$1:$A1),Sheet1!$F:$F,0)))

    check how many rows we have filled in and if the row number is greater than the max number of records, then it should return blanks (to avoid errors).

    INDEX/MATCH is what pulls the appropriate info. First we Index the range we want to get the data from... then we Match the row number we are on to the counting numbers in column F... the row numbers match 1 for 1.. when the row number exceeds the max number in the count, no more data is returned, and instead blanks are returned.

    If you add/remove data from the main sheet, the results should adjust accordingly.

  8. #8
    Registered User
    Join Date
    06-27-2010
    Location
    Boston, MA
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Vlookup returning mutiple values into multiple rows

    I think this is the easiest formula to use to return multiple results from a single query. It requires 1 formula in 1 cell. This formula will return the values directly to the right of the values you are looking up. In order to get the values 2, 3, 4 etc. to the right of the lookup value, simply exchange the "2" closest to the end of the function with 3, 4, 5, etc.

    =IFERROR(INDEX($A$9:$C$20,SMALL(IF($A$9:$A$20=$B$1,ROW($A$9:$A$20)),ROWS($A$3:$A3))-8,2),"")

    The -8 inputted near the end of the formula represents the number of rows from the top of the spreadsheet that the data field you are searching is. For example, if your data is stored in cells A8:D100, then you would make the value "-7". The column value you start with bears no significance, just the row value.

    Obviously this is just a sample function for you to manipulate based upon your specific spreadsheet. I could not find any easy, clear, and relatively simple formulas out there to achieve what I wanted, so I wrote my own.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1