Results 1 to 8 of 8

Thread: Need a help with a forumula

  1. #1

    Need a help with a forumula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need a vlookup formula or whatever formula to do the following to the attached spreadsheet
    The first worksheet is the sheet I want to pull information into. I want to search the2nd worksheet, User Profile, in the PID column, for each of the PIDs in the first worksheet (column B). I then want to pull that whole line of information into the Columns C through X. I have ensured that the first two will have results. Not all PIDs will be found.
    I hope I am making sense. My manager tells me she wants this for her 3 o’clock meeting tomorrow (Wednesday). I am not a happy camper cause this has 50,000 some lines of data. Can any one please help me?

    Test File.xlsx

  2. #2
    Conjurer CheshireCat's Avatar
    Join Date
    Dec 2011
    Location
    Victoria, Canada
    Posts
    120
    Articles
    0
    Excel Version
    Microsoft Excel 2013
    In cell C2, try this:

    Code:
    =INDEX('User Profile'!$A$2:$V$14,MATCH($B2,'User Profile'!$D$2:$D$14,0),MATCH(C$1,'User Profile'!$A$1:$V$1,0))
    Amend rows/columns as required for the size of the second worksheet, then autofill across/down.

  3. #3
    CheshireCat,

    Thank you for your efforts. I tried inputting the formula several times and I get #N/A.

  4. #4
    #N/A means no match, so the code isn't found.

    You could also try

    =VLOOKUP(B3,CHOOSE({1,2},'User Profile'!$D$2:$D$14,'User Profile'!$A$2:$A$14),2,FALSE)

  5. #5
    Bob,

    Yes, I know #N/A means no match. I see it all too often. I tried your formula, typed it in as you presented it above, and I get #N/A. Maybe this process just isn't possible. It has given me a headache and I hate this last minute stuff. I mean this could be done with a few days to do it, since there are over 50,000 lines of data, I would do it manually. But she wants it done right now, her way, with a VLookup.

    Thanks for your efforts!

  6. #6
    You are getting #N/A because there is nothing on User profile for some of the codes. BŁ doesn't match, B4 does. If you know what it means, what are you complaining about? What do want it to return when there is no match, it can't conjure up some fake value for you.

  7. #7
    This issue is closed. Thanks for all for your help.

  8. #8
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    I see that this issue is closed. However, for other people that want to know, if you expect to have results that are not listed in your lookup table thus returning a #N/A; you can get rid of this by simply adding an iferror() around your original formula.

    Code:
    =IFERROR(INDEX('User Profile'!$A$2:$V$14,MATCH($B5,'User Profile'!$D$2:$D$14,0),MATCH(C$1,'User Profile'!$A$1:$V$1,0)),"")
    this way if it returns an error it will simply report "" or blank text in the cell. This is useful if you just don't want to see a bunch of #N/A values.

Posting Permissions

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