+ Reply to Thread
Results 1 to 22 of 22

Google Sheets: How to combine v lookup with index match

  1. #1
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Google Sheets: How to combine v lookup with index match

    HI,

    i have an index match formula in K778:K779. which is working correctly. but i want to add in formula that if Q37 in order tracking is not filled then it should show result "pending" in K778:K779.
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: How to combine v lookup with index match

    Are you still using XL2007 as your profile indicates? I think not. Please amend your profile ( my XL2010 returns unknown functions...)

  3. #3
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: How to combine v lookup with index match

    i use excel only for little works, otherwise i works on googlesheet. but i am thinking one thing that when formula is working in xl2007 then why not in 2010

  4. #4
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: How to combine v lookup with index match

    anyway, plz provide the formula as the conditions i have explained in the post.

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: How to combine v lookup with index match

    I am sure you do not mean Q37 specifically but any cell in column Q ?
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

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

    Re: How to combine v lookup with index match

    The problem suggested by this in your formula when I open the workbook:

    @__xludf
    is that there is a user defined function (UDF called DUMMYFUNCTION) missing. This means that the original workbook must be macro-enabled with aa .xlm or .xlms extension. You have given us an .xlsx file that does not contain the UDF.

    You'll need to provide the workbook containing the VBA.
    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.

  7. #7
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: How to combine v lookup with index match

    Quote Originally Posted by JohnTopley View Post
    I am sure you do not mean Q37 specifically but any cell in column Q ?
    i have referenced Q37 because only row 37 belongs to ABC 555

  8. #8
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: How to combine v lookup with index match

    Quote Originally Posted by AliGW View Post
    The problem suggested by this in your formula when I open the workbook:



    is that there is a user defined function (UDF called DUMMYFUNCTION) missing. This means that the original workbook must be macro-enabled with aa .xlm or .xlms extension. You have given us an .xlsx file that does not contain the UDF.

    You'll need to provide the workbook containing the VBA.
    you are right. if i am not wrong then i want to say that as you all are talking index match what i have applied is not executable in excel 2007 or 2010

  9. #9
    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,416

    Re: How to combine v lookup with index match

    I don't understand your question.

    Provide a copy of the workbook with the UDF in it.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: How to combine v lookup with index match

    The __xludf.DUMMYFUNCTION is a replacement made by Google to make the file "readable" by Microsoft Excel.

    As far as I can ascertain there is no UDF!

  11. #11
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: How to combine v lookup with index match

    here is the link of my original google sheets file.

    https://docs.google.com/spreadsheets...gid=1797746507

  12. #12
    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,416

    Re: How to combine v lookup with index match

    It must be, otherwise my 365 would be able to read it!

    EDIT: Ah! It's a Google Sheet!!! No wonder we can't work with it. It must contain something incompatible with Excel.

    I am moving this to the correct forum section.

  13. #13
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: How to combine v lookup with index match

    but i have always got solution in my excel file downloaded by googlesheet as it is. and you are moving this post to another section, i have not got anysolution there before also.

  14. #14
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: Google Sheets: How to combine v lookup with index match

    i think that somehow can we combine v lookup that if that particular row is empty then "pending" otherwise whatever is in that row for column Q.

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: Google Sheets: How to combine v lookup with index match

    @Ali,
    If you are referring to my reply, I have no problem in XL2010 with the supplied file i.e. I do not get any error messages relating to formulae which include DUMMYFUNCTION references..

  16. #16
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: Google Sheets: How to combine v lookup with index match

    Quote Originally Posted by JohnTopley View Post
    @Ali,
    If you are referring to my reply, I have no problem in XL2010 with the supplied file i.e. I do not get any error messages relating to formulae which include DUMMYFUNCTION references..
    thanks for your confirmation. i always download the googlesheet as .xlsx and posts here. this is the first time i have ever heared that it is showing error. if this is so. then it should show error in my excel file also.

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

    Re: Google Sheets: How to combine v lookup with index match

    In K4 then copied down ARRAY formula

    Please Login or Register  to view this content.
    IN all formulas you have referred 1000th row as 1000. When drag down it will change. So I have changed it to $1000 so that when dragged reference will not change.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  18. #18
    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,416

    Re: Google Sheets: How to combine v lookup with index match

    Quote Originally Posted by JohnTopley View Post
    @Ali,
    If you are referring to my reply, I have no problem in XL2010 with the supplied file i.e. I do not get any error messages relating to formulae which include DUMMYFUNCTION references..
    I get this:

    =IFERROR(@__xludf.DUMMYFUNCTION("IF(B793="""","""", IF(ARRAY_CONSTRAIN( IFNA(FILTER(COLUMN('BATCH TRACKING'!K793:BF793),ISBLANK('BATCH TRACKING'!K793:BF793)),0),1,1)=19,""Completed"", IFERROR(INDEX('BATCH TRACKING'!A$3:BF$3,1,ARRAY_CONSTRAIN( FILTER(COLUMN('BATCH TRACKING'!K793:BF793),IS"&"BLANK('BATCH TRACKING'!K793:BF793)),1,1)),""No date data"")))"),"")

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Google Sheets: How to combine v lookup with index match

    ...as do I, Ali.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  20. #20
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: Google Sheets: How to combine v lookup with index match

    Quote Originally Posted by kvsrinivasamurthy View Post
    In K4 then copied down ARRAY formula

    Please Login or Register  to view this content.
    IN all formulas you have referred 1000th row as 1000. When drag down it will change. So I have changed it to $1000 so that when dragged reference will not change.
    this is showing in the result only "pending" for whole column.

  21. #21
    Forum Contributor
    Join Date
    08-07-2018
    Location
    india
    MS-Off Ver
    2007
    Posts
    233

    Re: Google Sheets: How to combine v lookup with index match

    Quote Originally Posted by AliGW View Post
    I get this:

    =IFERROR(@__xludf.DUMMYFUNCTION("IF(B793="""","""", IF(ARRAY_CONSTRAIN( IFNA(FILTER(COLUMN('BATCH TRACKING'!K793:BF793),ISBLANK('BATCH TRACKING'!K793:BF793)),0),1,1)=19,""Completed"", IFERROR(INDEX('BATCH TRACKING'!A$3:BF$3,1,ARRAY_CONSTRAIN( FILTER(COLUMN('BATCH TRACKING'!K793:BF793),IS"&"BLANK('BATCH TRACKING'!K793:BF793)),1,1)),""No date data"")))"),"")
    please look in only column K and b and c in batch tracking 2. and B,c,q in order tracking.

  22. #22
    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,416

    Re: Google Sheets: How to combine v lookup with index match

    I'm not helping with this, sorry, as I have no knowledge of Google Sheets. My response was merely as a reply to John.

+ 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. Conditional Formatting with index match between two tabs with Google Sheets
    By yourik in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 16
    Last Post: 12-14-2021, 08:10 PM
  2. Conditional Formatting with index match between two tabs with Google Sheets
    By yourik in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 12-14-2021, 05:58 AM
  3. Replies: 13
    Last Post: 12-13-2021, 05:54 PM
  4. [SOLVED] Index First Three Results That Match Criteria In Google Sheets
    By swordswinger710 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 03-27-2019, 05:06 PM
  5. Index/Match Help - Google Sheets
    By JenMasters84 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 04-25-2018, 02:52 PM
  6. [SOLVED] INDEX MATCH Lookup and Combine/Add Multiple Values
    By timmckean in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-01-2017, 09:47 AM
  7. [SOLVED] How can I combine Index, Match & Lookup function?
    By Shreyaanand in forum Excel General
    Replies: 5
    Last Post: 09-30-2015, 02:31 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