+ Reply to Thread
Results 1 to 12 of 12

VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

  1. #1
    Registered User
    Join Date
    01-10-2016
    Location
    Atlanta
    MS-Off Ver
    2013
    Posts
    5

    VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Hi Folks,

    First, thanks for helping to keep this forum up and running for those of us who are looking for help in using Excel. While I'm learning a lot by reading and watching how-to videos, there are still some problems that I can't figure out, in part because I don't really know how to refine my searches to specify precisely what I'm looking for. So my apologies if the question I ask is one that's already been answered.

    Here's my problem:

    I'm using VLOOKUP to cross-reference data on two different sheets, with the lookup value data on one sheet (let's say Sheet1) and the table array data on another (Sheet2). I have no problem getting VLOOKUP to populate Sheet1 with the relevant data from Sheet2. What I'm attempting to do is use the Fill Handle to apply VLOOKUP across multiple columns on Sheet1. This works easy-peasy if, when entering the second argument, I switch to Sheet2, highlight the relevant cells, and hit the comma key to have VLOOKUP enter the selected range into the function. When the function is fully written, I can use the Fill Handle to click and drag VLOOKUP across multiple columns and every cell populates with the required data. If, however, I name my table array using the Define Name option and then input that name into the VLOOKUP function, I am not able to successfully click and drag the Fill Handle across multiple columns without getting the #N/A error.

    In other words, using something like this allows me to use the Fill Handle to apply the VLOOKUP results across multiple columns:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    But using something like this doesn't:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Would any of you gents or ladies be able to tell me why that's happening? And is there a way for me to use the Fill Handle tool across multiple columns, if my table array argument has a defined name?
    Last edited by Menenius; 01-13-2016 at 09:09 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Well, if you want to copy the formula across (or down) then you should be using absolute addresses for the lookup table:

    VLOOKUP(A2,'Sheet2'!$A$2:$D$14,2,FALSE)

    Also, the A2 at the beginning should be $A2 if you are copying across, and you can change the 2 (3rd parameter of VLOOKUP) to something like:

    COLUMNS($A:B)

    so that changes as you copy it across.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    01-10-2016
    Location
    Atlanta
    MS-Off Ver
    2013
    Posts
    5

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Hey Pete,

    Thanks for your reply! Unfortunately, I wasn't able to accomplish what I wanted to using your suggestions. It might help if I describe the particular project I'm working on so that you can see how I'm employing the VLOOKUP function.

    The company I work for sells some, but not all, of the products manufactured by a particular vendor. This vendor has provided us with a full list of all its products and their related information (see the Vendor Price List). My company has made a list of the codes for the products we currently do NOT sell. These codes appear on the Not Found sheet. However, my company would also like to include the additional information about those products (description, unit cost, and MSRP) by copying them from the Vendor Price List sheet to the Not Found sheet. Now, I could type in the missing data myself, but in reality these vendor lists tend to have thousands of products. Therefore, I can use VLOOKUP to populate the missing data quickly and easily.

    vlookup_specified_range.jpg

    As you can see in the formula bar, I used a specified range for my table array argument. It is only by doing this that I'm able to use the Fill Handle to click and drag the VLOOKUP function across columns B through D on the Not Found sheet. I am not able to do this if I use a defined name for the table array. If I want to use a defined name, what I end up having to do is click and drag the VLOOKUP function from B2 to D2, and then manually change both the lookup value (from B2 to A2, from C2 to A2, etc.) and the column index number (from 2 to 3 to 4, and so on).

    It's not an especially agonizing problem, but I sure would like to combine my two desires, if possible: i.e., 1) to use a defined name for the table array and 2) to use the Fill Handle to apply the VLOOKUP function across the columns on the Not Found sheet. If that's not possible, no worries.

    I've attached a simplified copy of the sort of document I'm working on, just in case you or anyone else would like to fiddle around with it.
    Attached Files Attached Files

  4. #4
    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,917

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Try this, copied down and across...
    =INDEX('Vendor Price Listing'!B:B,MATCH('Not Found'!$A2,'Vendor Price Listing'!$A:$A,0))
    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

  5. #5
    Registered User
    Join Date
    01-10-2016
    Location
    Atlanta
    MS-Off Ver
    2013
    Posts
    5

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Hey Ford,

    Thanks for the suggestion. Still, this yields the same result that I get by simply highlighting A2 through D14 for VLOOKUP's second argument and hitting the comma key. Both this approach and yours get me the correct results, but neither relies on a defined name for the table array. What confuses me is that the name-defined table array contains the same cells as the selected/defined range. That is, both contain cells A2 through D14. It seems to me that VLOOKUP should perform identically regardless of which method is used.

    At any rate, I appreciate both you and Pete for taking a stab at this for me. As I implied before, not being able to resolve this issue is not a major problem for me. It's more of a curiosity than an inconvenience.

  6. #6
    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,917

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Why do you need to use a named range? With INDEX/MATCH like that, you can use full-column ranges

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Try it like this:

    VLOOKUP($A2,table,COLUMNS($A:B),FALSE)

    then copy across.

    Hope this helps.

    Pete

  8. #8
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Another try

    B2=VLOOKUP($A2,'Vendor Price Listing'!$A$1:$D$14,MATCH('Not Found'!B$1,'Vendor Price Listing'!$A$1:$D$1,0),FALSE) and drag down and over the right cells.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  9. #9
    Registered User
    Join Date
    01-10-2016
    Location
    Atlanta
    MS-Off Ver
    2013
    Posts
    5

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Folks,

    Thanks a bunch for your continued help. My apologies for not replying earlier. Muphy's Law struck me numerous times in the past couple of days, so I've been busy putting out fires here and there. I will give your suggestions a shot and get back to you ASAP. I just didn't want you all to think I was ignoring your suggestions.

  10. #10
    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,917

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Thank you for the feedback, looking forward to hearing from you soon

  11. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    With the data on the Vendor Price Listing sheet converted to a Table
    Try this in B2 and filled right/down on the Not Found tab.

    =INDEX(Table1[Item Description],MATCH($A2,Table1[[Product Code]:[Product Code]],0))

    Referencing a column in a Table like this is relative (it will increment when dragged right)
    TableName[ColumnName]
    Equivalent to A$1:A$10

    Referencing a column in a Table like this is Absolute (it will NOT increment when dragged right)
    TableName[[ColumnName]:[ColumnName]]
    Eqivalent to $A$1:$A$10


    Hope that's helpful.

  12. #12
    Registered User
    Join Date
    01-10-2016
    Location
    Atlanta
    MS-Off Ver
    2013
    Posts
    5

    Re: VLOOKUP and Fill Handle Problem: Selected Range vs. Named Range

    Ford, to answer your question from earlier, I don't actually need to use a named range; I just happened to notice that the function failed to increment (hat-tip to Jonmo1 for the vocab lesson) when dragged to the right, if I used a named range rather than something else. That curiosity started to nag at me, so I looked for some help and found this little oasis.

    At the end of the day, I think all of your responses have turned out to be helpful in a way I didn't anticipate, and perhaps that's because I'm still quite a novice when it comes to using Excel.

    Does this mean I can give you all reputation points in lieu of buying you a round of drinks?

+ 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. Named Range Problem within a VLookup
    By Renleff in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-15-2014, 05:47 PM
  2. Problem using an named range in VLOOKUP
    By stubbsj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2013, 01:18 PM
  3. Add row to a named range selected in a dropdown.
    By ryan12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-07-2010, 01:55 PM
  4. Selected Cell within named range?
    By negcx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-23-2008, 09:08 PM
  5. Insert range-named row at user-selected row
    By cursor in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-04-2008, 03:50 PM
  6. fill Formula1 with named range
    By Hammer_757 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-09-2007, 04:52 PM
  7. Compare a selected Range with a Named range and select cells that do not exist
    By PCLIVE in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2005, 03:05 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