+ Reply to Thread
Results 1 to 16 of 16

VLOOKUP - what am I doing wrong

  1. #1
    Registered User
    Join Date
    10-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    8

    VLOOKUP - what am I doing wrong

    Please Login or Register  to view this content.
    HTML Code: 
    I keep using a VLOOKUP to reference the second sheet, and if the number from the first sheet is found in columns 2-5 of the second sheet, I want it to return to the first sheet a column with the number that corresponds in the first column on the second sheet. All I can ever make my function do is return to me the same number that is located in the first example directly next to it, ie:

    Please Login or Register  to view this content.
    This is what I have been using on "Sheet 1" : =VLOOKUP(A2,Sheet2!$B$2:$F$1001,1,FALSE)
    Attached Files Attached Files
    Last edited by ssmaster; 10-15-2013 at 07:39 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: VLOOKUP - what am I doing wrong

    Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: VLOOKUP - what am I doing wrong

    for starters, your vlookup is only referencing one column, you are saying if the item you are looking up is in columns B through F, look for it only in col B - that is what the "1" is telling it.
    =VLOOKUP(A6,Sheet2!$B$2:$F$1001,1,FALSE)
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Registered User
    Join Date
    10-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: VLOOKUP - what am I doing wrong

    Quote Originally Posted by Sambo kid View Post
    for starters, your vlookup is only referencing one column, you are saying if the item you are looking up is in columns B through F, look for it only in col B - that is what the "1" is telling it.
    =VLOOKUP(A6,Sheet2!$B$2:$F$1001,1,FALSE)
    The item I want returned is in column a, but I need to be searching in columns b-f for a match to what is in sheet column a.

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: VLOOKUP - what am I doing wrong

    Now that I look at your spreadsheet and your "AFTER" worksheet, I'm more confused.
    Are you looking to find GS-214619 in col B of Sheet2 and pull the corresponding value from col A in the same sheet?

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: VLOOKUP - what am I doing wrong

    we cross posted.
    So based on that I think you need an index and match formula although there is a reverse vlookup but I don't think it is the best thing for your issue.
    Problem is I'm not very good at using index and match so sixth sense can help you better.

  7. #7
    Registered User
    Join Date
    10-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: VLOOKUP - what am I doing wrong

    I want to pull column a of Sheet2 into column b of sheet 1.

  8. #8
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: VLOOKUP - what am I doing wrong

    In B2 Cell of Sheet1

    =INDEX(Sheet2!$A$2:$A$1001,SUMPRODUCT(1*MAX((Sheet2!$B$2:$F$1001=A2)*ROW(Sheet2!$B$2:$F$1001))))

    Drag it down...

  9. #9
    Registered User
    Join Date
    10-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: VLOOKUP - what am I doing wrong

    That looks a lot more complicated than what I came up with. I'll let you know how well it works when I get to work. Thanks.

  10. #10
    Registered User
    Join Date
    10-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: VLOOKUP - what am I doing wrong

    Quote Originally Posted by :) Sixthsense :) View Post
    In B2 Cell of Sheet1

    =INDEX(Sheet2!$A$2:$A$1001,SUMPRODUCT(1*MAX((Sheet2!$B$2:$F$1001=A2)*ROW(Sheet2!$B$2:$F$1001))))

    Drag it down...
    This is almost working. I am getting correct results when a correct result exists, but when a result doesn't exist (as many of the entries don't have data), it is bringing back a random (it seems to be in sequential order) entry from sheet 2 column 1. If no correct entry exists, I want it to return a N/A or blank entry etc;

  11. #11
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: VLOOKUP - what am I doing wrong

    No it won't get any error result I believe... Can you please show it in excel in which cell it is failing for better understanding...

  12. #12
    Registered User
    Join Date
    10-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: VLOOKUP - what am I doing wrong

    Please Login or Register  to view this content.
    In this example, the first five entries are correct. The sixth entry brings back 08QBE000033, but that entry is incorrect. GS-162791 does not exist in sheet2. Entries 7-9 are also incorrect. Entry 10 *IS* correct. I should get a N/A or blank entry next to 08QBE000033.

    And I had to edit your code a bit, I used =INDEX(Sheet2!$A$1:$A$1002,SUMPRODUCT(1*MAX((Sheet2!$B$1:$F$1002=A2)*ROW(Sheet2!$B$1:$F$1002))))
    Last edited by ssmaster; 10-15-2013 at 09:57 AM.

  13. #13
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: VLOOKUP - what am I doing wrong

    Please show it in excel please........

  14. #14
    Registered User
    Join Date
    10-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: VLOOKUP - what am I doing wrong

    I have attached as version 2. Thank you!
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    10-15-2013
    Location
    New York
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: VLOOKUP - what am I doing wrong

    I have attached as version 2. Thank you.

  16. #16
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: VLOOKUP - what am I doing wrong

    Thanks for the attachment file try the revised formula

    In B2 Cell

    =IF(SUMPRODUCT(1*MAX((Sheet2!$B$1:$F$1002=A2)*ROW(Sheet2!$B$1:$F$1002))),INDEX(Sheet2!$A$1:$A$1002,SUMPRODUCT(1*MAX((Sheet2!$B$1:$F$1002=A2)*ROW(Sheet2!$B$1:$F$1002)))),"")

    Drag it down...

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] What's wrong with my vlookup?
    By preveo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-28-2013, 06:47 AM
  2. [SOLVED] Don't know what's wrong with Vlookup
    By timko92 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 08-06-2013, 04:55 PM
  3. [SOLVED] Vlookup what am I doing wrong?
    By jazz56 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 06-15-2013, 03:13 PM
  4. vlookup, what am I doing wrong?
    By ufo_pilot in forum Excel General
    Replies: 3
    Last Post: 08-09-2005, 01:05 PM
  5. [SOLVED] What wrong with VLOOKUP formula
    By TARZAN in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-31-2005, 07:06 PM

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