+ Reply to Thread
Results 1 to 2 of 2

Google Sheets: Issue with VLookup error

  1. #1
    Registered User
    Join Date
    11-03-2019
    Location
    Melbourne, Australia
    MS-Off Ver
    2019
    Posts
    1

    Google Sheets: Issue with VLookup error

    I have this spreadsheet attached

    And this is the formula I am trying to use - =LOOKUP(C12,'Client Results'!$A:$A,'Client Results'!$D:$D)

    In a nutshell I have a google forms questionnaire (Which I am not allowed to post the link for as I have not been a member for long enough) - Which is collecting data into a google sheets spreadsheet. From that spreadsheet I have created another template for each client that has an Import range function which will be on a hidden sheet (This is the formula used =importRange("1ws1reC1USbXQSD_gxY9oeJ_xJ56nO_Pt4fcANCf6ttM","Form Responses 1!B:S") ) and I have created another sheet on that spreadsheet with an array function specific to each client (=ARRAY_CONSTRAIN(ARRAYFORMULA(IFNA(INDEX(Feed!B:B,MATCH($B$1,Feed!$A:$A,5))," ")), 1, 1) )

    I am trying to import the data from the Client results sheet into the tracking sheet using the lookup function if it matches the same date as the client results sheet date. The data is not pulling in and I cannot work out what I am doing wrong. The more I keep fiddling the more mistakes I keep making.

    Ultimately I am trying to keep all the data neat and clean over the 12 week period.

    If the lookup function is incorrect I am down for another solution.

    I am happy to share the google sheets and google form links via private message or if this forum allows me to post it, if it is easier
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    671

    Re: Google Sheets: Issue with VLookup error

    What are you trying to achieve with MATCH($B$1,Feed!$A:$A,5)? Google Sheets is identical to Excel in many ways, one of which is 3rd arguments to MATCH(). In both, you could use 5 as 3rd argument, but they're treated the same as 1, which is the default, so no need to include a 3rd argument.

    Should Feed!A2 contain Option 1, or should it contain a client number as A3:A4 do? I'd also note that Feed!C2:C4 isn't in either ascending or descending order. Which leads me to suspect the data in the Feed worksheet needn't be sorted. In which case you should be using MATCH() with 3rd argument 0. Also, INDEX(one_range,MATCH(single_value,another_range,0)) will only ever return a single value. If you want to return ALL records from the Feed worksheet into the Client Results worksheet, use FILTER(). That is, this one formula

    'Client Results'!B3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    returns all records for the client number in 'Client Results'!B1 from the Feed worksheet's columns B to R in the SAME ORDER as those records appear in the Feed worksheet. If you're using LOOKUP, you need those records sorted on Feed!C:C in ascending order, so sort the records in Client Results by changing the formula above to

    'Client Results'!B3
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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] Google Sheets: Sum up a vLookup formula within an ArrayFormula
    By juno612 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 10
    Last Post: 08-15-2020, 06:10 PM
  2. Google Sheets - IMPORTRANGE Error
    By Mrneski in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 03-05-2020, 10:56 PM
  3. [SOLVED] Vlookup for another sheet in google sheets
    By turkanet in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 12-02-2018, 04:42 AM
  4. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  5. [SOLVED] Lookup function error (google sheets)
    By WAAZUPEE in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 12-14-2017, 11:47 AM
  6. [SOLVED] Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]
    By Jietoh in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 03-23-2017, 01:35 PM
  7. Issue - Security Error Prompt in Google Chrome
    By jewelsharma in forum Suggestions for Improvement
    Replies: 6
    Last Post: 02-08-2016, 05:13 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