+ Reply to Thread
Results 1 to 9 of 9

Vlook up help, i think

  1. 09-24-2012, 05:42 PM #1
    scotte57
    scotte57 is offline
    Registered User
    Join Date
    09-22-2012
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Vlook up help, i think

    I have written most of my vlookup, but found that it is not robust enough to provide me the information I need. I am performing the following equation

    =IF(ISERROR(VLOOKUP($A2,'[Backlog 10-5.xls]Old Backlog'!$A$2:$U$20000,9,FALSE))," ",(VLOOKUP($A2,'[Backlog 10-5.xls]Old Backlog'!$A$2:$U$20000,9,FALSE)))

    What I have come to learn in developing this equation is that I can have 2 or more identical A2 column itemes, but to tell them apart, you need to use B2, which is a numerical value for the line number. How do I incorporate another Vlookup(or whatever function) that will say if A2 on worksheet one matches work Sheet 2, to then look at B2 and pull back/display on worksheet 1 the correct value from column number 9?
    Reply With QuoteRegister To Reply
  2. 09-24-2012, 05:48 PM #2
    scotte57
    scotte57 is offline
    Registered User
    Join Date
    09-22-2012
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Vlook up help, i think

    Sorry forgot to attach my example.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 09-24-2012, 05:53 PM #3
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Vlook up help, i think

    try this..

    =IF(ISERROR(INDEX([Backlog 10-5.xls]Old Backlog'!$A$2:$U$20000,MATCH(A2&B2,[Backlog 10-5.xls]Old Backlog'!$A$2:$U$20000&ROW($A$2:$A$20000),0),9)),"",INDEX([Backlog 10-5.xls]Old Backlog'!$A$2:$U$20000,MATCH(A2&B2,[Backlog 10-5.xls]Old Backlog'!$A$2:$U$20000&ROW($A$2:$A$20000),0),9))

    Confirm with Ctrl+Shift+Enter and not just Enter
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  4. 09-24-2012, 08:12 PM #4
    scotte57
    scotte57 is offline
    Registered User
    Join Date
    09-22-2012
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Vlook up help, i think

    I cannot seem to get it to work. I get an error highlighting the ([Backlog 10-5.xls]Old Backlog' after the index on the first line. For my example can I just call those pieces my columns like the original way I wrote out the vlook up? Doesn't seem to mater if i use the CTRL+Shift+enter or not.
    Reply With QuoteRegister To Reply
  5. 09-25-2012, 01:10 AM #5
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Vlook up help, i think

    Use..

    =IF(ISERROR(INDEX(old!$A$2:$U$20000,MATCH(A2&B2,old!$A$2:$A$20000&old!$B$2:$B$20000,0),9)),"",INDEX(old!$A$2:$U$20000,MATCH(A2&B2,old!$A$2:$A$20000&old!$B$2:$B$20000,0),9))

    Confirm with Ctrl+Shift+Enter

    See attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 09-25-2012, 11:55 AM #6
    scotte57
    scotte57 is offline
    Registered User
    Join Date
    09-22-2012
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Vlook up help, i think

    I typed it in exactly, and when I hit the confirm with Ctrl+Shift+Enter it opens up another window and that says Update Values:old, and shows me my documents library. The sample attached works perfectly so I am not sure what I am doing wrong.

    Sorry, think I got it, on my real data, I am using the 2nd worksheet called Old Backlog, so I should just have to change the part of the above equation from "old" to "Old Backlog" and I should be good to go, right?
    Last edited by scotte57; 09-25-2012 at 12:00 PM.
    Reply With QuoteRegister To Reply
  7. 09-25-2012, 02:57 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Vlook up help, i think

    if it opens your documants window, that means that you have a sheet name in your formula that you do not have in your work book. chech the typing and make sure the names are identical (including spaces)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  8. 09-25-2012, 06:41 PM #8
    scotte57
    scotte57 is offline
    Registered User
    Join Date
    09-22-2012
    Location
    california
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Vlook up help, i think

    I have tried multiple times, just doesn't seem to work, not sure what the heck I am doing wrong.
    Reply With QuoteRegister To Reply
  9. 09-25-2012, 07:49 PM #9
    jeffreybrown
    jeffreybrown is offline
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Vlook up help, i think

    We would love to continue to help you with your query, but first, before we can proceed, please see the forum rules about proper thread titles and adjust accordingly...

    Please send me a PM when complete and this post will be removed…

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    HTH
    Regards, Jeff
    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