+ Reply to Thread
Results 1 to 5 of 5

Vlookup not working in part of a column.... WTH

  1. #1
    Forum Contributor
    Join Date
    10-10-2015
    Location
    nj
    MS-Off Ver
    Office 2021
    Posts
    203

    Vlookup not working in part of a column.... WTH

    Hi all,

    In the attached Vlookup is not working through the whole column. Example see cell C5010. The vlookup data is in column AG, AH. You can see see it is searching based on data in A5010 which is in the data column.

    So what the heck....
    Attached Files Attached Files

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup not working in part of a column.... WTH

    This should work for you. (I'm a little biased towards INDEX MATCH).

    C4375 =INDEX(AH:AH,MATCH(A4375,AG:AG,0))

    Drag down column C.

    The ranges in your VLOOKUP did not include all of the data in columns AG and AH.

    If you prefer to use VLOOKUP, you can use this:

    =VLOOKUP(A4375,AG:AH,2,0)

    If you want to specify the exact range where the data is, you can use this:

    =VLOOKUP(A4375,$AG$4:$AH$6314,2,0) <--eww gross
    Last edited by 63falcondude; 05-31-2017 at 01:59 PM.

  3. #3
    Forum Contributor
    Join Date
    10-10-2015
    Location
    nj
    MS-Off Ver
    Office 2021
    Posts
    203

    Re: Vlookup not working in part of a column.... WTH

    Hi Falcon thanks. Any reason it was excluding those rows in C? Just for sake of mind.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Vlookup not working in part of a column.... WTH

    Quote Originally Posted by forestavekids View Post
    Hi Falcon thanks. Any reason it was excluding those rows in C? Just for sake of mind.
    You're welcome. Happy to help.

    Your formula was only looking at AG$1:AH$5200.

    I'm not sure why you only set it to row 5200 but that explains why it wasn't fully working.

  5. #5
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Vlookup not working in part of a column.... WTH

    If you are trying to extract the values within the same sheet (Vlookup named sheet), select the range from AG4 to AG6314.
    Start practicing Index Match formula, so you can extract values even on both sides. Vlookup cannot extract values on the left side or behind the lookup value column.

    Write formula like this:
    Index(Select the range of cells where the values to be extracted available,Match(look up value- select the cell which you would like to match up with, Lookup array- select the range of cells where the lookup values are available,0))

    INDEX($AH$4:$AH$6314,MATCH(A4375,$AG$4:$AG$6314,0))

+ 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] VLookup not working in certain column but works in others
    By afrabat in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-28-2014, 03:20 PM
  2. Replies: 1
    Last Post: 05-27-2014, 02:40 PM
  3. [SOLVED] Vlookup second column if first is empty not working
    By tlaing in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2014, 04:01 PM
  4. [SOLVED] Part of If statement not working
    By Boo123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-28-2013, 03:38 PM
  5. [SOLVED] Vlookup referencing one column that contains part numbers in both.
    By crafty_girl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-18-2006, 02:35 PM
  6. [SOLVED] Copying Part of a row down part of a column
    By Not Excelling in forum Excel General
    Replies: 3
    Last Post: 01-07-2006, 04:10 AM

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