+ Reply to Thread
Results 1 to 10 of 10

Index Match Vlookup Type formula off of Drop Down Menu/Named Range

  1. #1
    Registered User
    Join Date
    08-07-2013
    Location
    Texas
    MS-Off Ver
    Microsoft Office/Excel 2019 for Mac
    Posts
    39

    Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    I just learned how to to link to a graphic that I can change by clicking the associated customer in a drop down menu. The creator of the formula outlined how to create a Dropdown with validation list then a Named range, so this gave me an idea.

    What I have is a workbook with multiple worksheets. The first worksheet is always a Reference, the remaining worksheets are labeled for each customer accounts. Each of these customer account worksheets are set up the same with the same column headers, row headers and basic format. Within the cells is the data that pertains just to that customer (product, pricing, dates of programs, etc.).

    I'd like to have a worksheet be utilized as a 'Handout' that I customize when I am working on a particular day with certain customers. For instance, if I am going to be working with CustomerA for the day, I'd like to be able to use the Handout worksheet, click a dropdown menu within choosing the CustomerA worksheet and the Handout could fill in certain cells with data from the CustomerA worksheet. I know I can do VLookup for various cells but I'm wondering if I can have a blanket VLookup activate for multiple cells? I always create worksheets with a column at the beginning for a common product item number. Out of 1000 product numbers, different customers use different ones so the product information could fill in by referencing this.

    If it helps to create a test workbook, please let know and I'll do this.

  2. #2
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    96

    Re: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    I am not entirely sure I understand what you are looking for, especially regarding the drop-down box, but it sounds very similar to various "forms/reports" I have - where I fill in all a subcontractor's information from a "database" sheet of subcontractor information - I use the "automatic form-filling" vlookup to fill in information for our accounts department or for meeting minutes with the subcontractors

    The way I do it is to create a three or four digit code for each subcontractor and then have a cell off the printable page of the "form" where I type in this three or four digit code and then I point all my Vlookup "lookup value" portions of the vlookup formula to this cell.

    If all your data is on different sheets, rather than on one like mine, you would have to create a name for all your sheets (by typing the names of your sheets into a column (say in a new sheet) and creating an array vlookup formula (if memory serves) to use vlookup over multiple sheets: I know I have managed it, but the formula is tricky for someone new to more intricate formulas like me!

    I think you would need to upload a sample workbook to give people a better idea of what you are trying to do

  3. #3
    Registered User
    Join Date
    08-07-2013
    Location
    Texas
    MS-Off Ver
    Microsoft Office/Excel 2019 for Mac
    Posts
    39

    Re: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    Ha ha ha. I was getting frustrated because I couldn't get my workbook to attach and it was there all along.

    QSGuy - It sounds like the process you use could work for me IF I could use a Dropdown that would then choose the data from each different spreadsheet tab based on what I choose the drop down. I just don't know how to link up the drop down menu to update the VLOOKUPs to fill in with data from the chosen spreadsheet.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by msdclark; 12-03-2020 at 01:18 PM.

  4. #4
    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: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    The file is attached, so don't worry about it.

    Pete

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    The file is attached below your screenshot!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    96

    Re: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    If I have understood you correctly, you want to search the different worksheets with the dropdown or even the different wines?

    That is not necessary with a defined name “array” Vlookup.

    I have assumed that the ASW# is a unique reference for each wine – and in the attached, once that number is added into a box in column C, all the info for that wine is brought up in the Handout page columns. I have copied your handout sheet in case the method here is not what you were looking for – such that the original is still there.

    I have assumed that United, Ralston and Twin are suppliers into the business. Notice the new sheet “Suppliers” this contains a list of worksheets in the workbook (see the highlighted cells) that is used to create a “named range” of all worksheets. When this “name” is used in a formula it effectively treats all the info in each worksheet as if it were in one worksheet. I have left a few extra lines in case you have more sheets in your actual workbook, but if you need to add more worksheets into the named range, go to the “Formulas” tab and click on Name Manager and then Edit - you will see the range noted – just type in your extended range line number. You can also change the name of the range if “Suppliers” does not suit. Make sure you click save.

    Notice the curly brackets round the formulas I have added – this tells you that the formula is an “array” formula and that you need to hold Control, Shift and Enter to change, not just enter. If you want to copy the formula down the page, do so in the box in the corner of the cell, rather than by copy/paste, as you will have to Control Shift Enter for each cell if you do it by copy/paste.

    I have filled in the columns you had, but if you want to copy the formula into other columns remember to change the column VLookup result column in the normal way and to Control Shift Enter. As the formula is wrapped in an IFERROR formula that defaults to blank if there is an error, if you get blank cells after copying the formula the chances are it is because you have not entered Control Shift Enter as without that action the formula brings up a REF# error that is blanked by IFERROR.

    Hope this is of use – if this is not what you were looking for, we will have to think again.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    96

    Re: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    The one thing I would say is that, in my experience, array formula use up a lot of memory and in a spreadsheet with so many array formula you may find that the worksheet is slow to respond or save.

    There may be a more efficient means to get the desired result, but I am only a beginner in terms of the more advanced formulas, so this is only way I know. Others here may be able to come up with a more efficient means of achieving the result required

  8. #8
    Registered User
    Join Date
    04-08-2019
    Location
    Manchester
    MS-Off Ver
    2016
    Posts
    96

    Re: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    The one thing I would say is that, in my experience, array formula use up a lot of memory and in a spreadsheet with so many array formula you may find that the worksheet is slow to respond or save.

    There may be a more efficient means to get the desired result, but I am only a beginner in terms of the more advanced formulas, so this is only way I know. Others here may be able to come up with a more efficient means of achieving the result required

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    In D6 then copied down and across till last column

    =IFERROR(VLOOKUP($C6,INDIRECT("'"&$B$2&"'!$B:$AH"),1+COLUMNS($D6:D6),FALSE),"")

    Pl Note that Sheet names are corrected in Logos Sheet , which are used for drop down Validation.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Registered User
    Join Date
    08-07-2013
    Location
    Texas
    MS-Off Ver
    Microsoft Office/Excel 2019 for Mac
    Posts
    39

    Re: Index Match Vlookup Type formula off of Drop Down Menu/Named Range

    Obviously my brain is overtaxed because I cannot comprehend the directions right now. I promise I am reading but will start this tomorrow when my mind is fresh again.

+ 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. Index Match with Indirect and Named Range
    By hikari8 in forum Excel Formulas & Functions
    Replies: 44
    Last Post: 01-31-2020, 07:50 AM
  2. [SOLVED] Why does Index and Match Formula work for Named Range.. Except The Last Rows?
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-27-2018, 02:26 PM
  3. Named range in Index & Match
    By edward_glyver in forum Excel General
    Replies: 10
    Last Post: 04-29-2016, 08:11 AM
  4. [SOLVED] INDEX MATCH VLOOKUP Named Range
    By overbomb in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-03-2015, 12:56 PM
  5. [SOLVED] Reference to named range to be used in index match formula!! Help Please!!
    By Optimum in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-24-2013, 06:54 AM
  6. SOLVED! named range drop down list sum index
    By superspurs in forum Excel General
    Replies: 2
    Last Post: 07-21-2011, 04:39 PM
  7. Named range lookup using Index and match
    By Sirrob01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-01-2007, 11:15 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