+ Reply to Thread
Results 1 to 17 of 17

Vlookup multiple values for same ID

  1. 01-14-2008, 08:50 AM #1
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123

    Vlookup multiple values for same ID

    Hi All

    I am trying to do a lookup table to match the entry rows for the same ID number on the two worksheets, please see attached. The worksheets are acually 10000+ rows, so I've just taken a snap shot.

    When I try to do a lookup it matches the ID but then only returns the first row values for each ID Number. I am basically trying to check what is on one report and not the other,

    Thanks.

    cortlyn
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-14-2008, 08:57 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    It is not clear what you are actually looking for.

    Can you repost your sample showing some expected results.
    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.
    Reply With QuoteRegister To Reply
  3. 01-14-2008, 09:34 AM #3
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    Hi NBVC

    Please seee attahced file with the lookups.

    Thanks

    Cortlyn
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 01-14-2008, 09:36 AM #4
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    Oops sorry, I posted the wrong file on last post, try this one.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 01-14-2008, 10:04 AM #5
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Not exactly sure I got what you mean...but see attached...

    N.B. I only inserted formulas for 1st 16 rows...since I was exceeding forum attachment limit of 100kb. You will need to copy those formulas down the rest of the way.

    I basically looked for exact matches between ID, start date and end date and reposted them in the results columns.

    Note: The formulas are array formulas and are confirmed with CTRK+SHIFT+ENTER to show the { } brackets.



    If you make adjustments, you must reconfirm with those keys.

    You can also make the sheet more efficient, by just displaying if an exact match was found or not....

    e.g. 
    Please Login or Register  to view this content.
    copied down will have similar effect.
    Attached Files Attached Files
    Last edited by NBVC; 01-14-2008 at 10:07 AM.
    Reply With QuoteRegister To Reply
  6. 01-14-2008, 10:14 AM #6
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    Thank you very much. I will have a play with it now.

    Basically we are parralell running reports of the "same" info taken off 2 systems running different software and i am trying to make sure that the data matches.

    Regards

    Cortlyn
    Reply With QuoteRegister To Reply
  7. 01-14-2008, 11:02 AM #7
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    Hi NVBQ

    Hi I am having trouble copying the formula into a new worksheet as the snap shout I took was from the middle of the spreadsheet. I have attached the first 100 or so rows. Please can you assiast with getting the formula into UAT Absence row 2.

    Thanks

    Cortlyn
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 01-14-2008, 11:23 AM #8
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    The problem is that your dates in the UAT Absence sheet are entered as text strings and are not true dates...so they don't match the dates in the other sheets.

    To remedy this, select column C in the UAT Absence sheet and go to Data|Text to Columns.

    Click Next and click Next again.

    From the top right area labeled: Column data format, select MYD from the Date drop down field.

    Now, the formulas should work.... see attached "fixed" sample.

    ....it's NBVC by the way.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 01-14-2008, 11:41 AM #9
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    Thank you very much for your help.

    Apologies for the typo .

    Regards

    Cortlyn
    Reply With QuoteRegister To Reply
  10. 01-14-2008, 11:45 AM #10
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    No problem...

    you probably figured it out, but you need to repeat the Data|Text to columns fix on column D also.
    Reply With QuoteRegister To Reply
  11. 01-14-2008, 12:50 PM #11
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    =IF(ISNUMBER(MATCH($A2,'PS Query'!$A$1:$A$11727,0)),INDEX('PS Query'!A$1:A$11727,MATCH(1,('PS Query'!$A$1:$A$11727=$A2)*('PS Query'!$E$1:$E$11727=$C2)*('PS Query'!$F$1:$F$11727=$D2),0)),"")

    Hello again NBVC

    I have tired to change the formula to read as above to look at the entire spread sheet, but I can only get to copy down to row 100, which was the last row of the example you gave. What am I doing wrong?

    Thanks

    Cortlyn
    Reply With QuoteRegister To Reply
  12. 01-14-2008, 12:51 PM #12
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    I have added the remainder of the spreadsheets to a copy of the example. Then applied that formula.
    Reply With QuoteRegister To Reply
  13. 01-14-2008, 01:10 PM #13
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Did you confirm the formula with the CTRL, SHIFT and ENTER keys?

    After you type or copy the formula in a cell, hold the CTRL and Shift keys down and press the ENTER key.

    YOu should see curly brackets { } appear around the formula. Then you can copy down.
    Reply With QuoteRegister To Reply
  14. 01-14-2008, 01:23 PM #14
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    Hiya

    Here is a copy of a section of the spreadsheet where its going wrong for me.

    Ta.

    Cortlyn
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  15. 01-14-2008, 02:07 PM #15
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Are you sure the workbook you are looking up has those ID's in it to match too... it looks like the last matched ID is the same as your 100 line sample you displayed earlier....

    Make sure you are looking up the right workbook with all items....

    other than that, if you copied the formula down and you adjusted all those dates in columns C and D, then it should work fine.
    Reply With QuoteRegister To Reply
  16. 01-14-2008, 05:02 PM #16
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    Hi Thanks

    It works at last, I hadn't formatted all the columns.

    Rgards

    Cortlyn
    Reply With QuoteRegister To Reply
  17. 01-14-2008, 08:12 PM #17
    Cortlyn
    Cortlyn is offline
    Forum Contributor
    Join Date
    11-06-2007
    Posts
    123
    Thank you very much for all your help, NVBC.

    Regards

    Cortlyn
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1