+ Reply to Thread
Results 1 to 5 of 5

Script to Lookup and Return Multiple Values

  1. #1
    Registered User
    Join Date
    05-29-2018
    Location
    Portland, OR
    MS-Off Ver
    2016
    Posts
    8

    Script to Lookup and Return Multiple Values

    End Goal = Populate 2019 Plan Tab with values (Get location's budget value from Budget Tab and put it in the corresponding Period based on Schedule Tab & Calendar Tab)

    Macro =
    a) Find Location from 2019 Tab in Schedule Tab
    b) Get date from Column B on Schedule Tab
    c) Search date in Column A on Calendar Tab
    d) Get Period from Column B on Calendar Tab
    e) Search location from Column A in Schedule Tab
    f) Paste value in corresponding period based on result from d)

    Notes:
    - Each location will essentially 4 dates in the Schedule Tab, see location AD & AF for an example (in real life there will be 13 periods in total but all the columns from the schedule remain the same)

    I tried a couple of excel formulas but because the locations are across multiple columns and in more than 1 cell, the lookup returns an error or it returns just the first indication.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    24,825

    Re: Script to Lookup and Return Multiple Values

    Hi,

    This is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Therefore upload your real workbook (or at least a cut down copy), and manually add the results you expect to see. Clearly identify which is original data and which are the results and in a note clearly explain how you have arrived at your results.

    Re b) when you say get date, which date? The current date..some other...
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-29-2018
    Location
    Portland, OR
    MS-Off Ver
    2016
    Posts
    8

    Re: Script to Lookup and Return Multiple Values

    Attached is the real workbook (cut down), in which the data is specific to a company ...

    The results I expect to see are on the first tab. All the other tabs are original data. My high level expectation is to run the macro and produce the data on the first tab (highlighted in yellow), all other data is original and will already be in the workbook when I run the macro. Date is already listed on the Schedule tab (column B) and Calendar tab (column A). They are both just all dates in a year.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    6,940

    Re: Script to Lookup and Return Multiple Values

    Here is a formula based proposal.
    On the 'Schedule' sheet column Z is populated with the periods using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    AA2:MB2 are populated by transpose copying the store numbers from the '2019 Plan' sheet.
    AA3:MB89 are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    On the '2019 Plan' sheet columns C:O are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,458

    Re: Script to Lookup and Return Multiple Values

    This works
    ARRAY formula in C4 then dragged across
    Please Login or Register  to view this content.
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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