+ Reply to Thread
Results 1 to 7 of 7

Problems applying INDEX-MATCH-MATCH function on other data

  1. #1
    Registered User
    Join Date
    05-04-2015
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    16

    Problems applying INDEX-MATCH-MATCH function on other data

    Dear all,

    First, I'd like to refer you all to the attachment, which contains my solution up to now:

    Problem for review by forum.xlsx

    Tab 1 shows 3 parts: green, yellow and blue.
    The green part is the 'raw' data; random numbers (examples) with 3 criteria/characteristics: date, color and name (column criteria).
    The yellow part being my current solution (which works, but weakness here is the column component of the VLOOKUP, which is a number instead of a formula, and therefore vulnerable to data changes).
    It gets interesting at the blue part, which is my target solution: a dropdown in the upper-left corner and an INDEX-MATCH-MATCH to collect data from the green part. Using the dropdown to change value 'Name' works perfectly; a nicely tailored solution.

    Finally the problem: when I apply this solution on my target data (which I unfortunately cannot share, but has the same format, only much more extensive), I get the ever returning '#N/A'.
    I've made sure that the formula specifics are adjusted to the target data sheet, so I have no idea why it doesn't work.
    Hard to see without the real data, I know, but do you guys have an idea what could have gone wrong?

    Lots of thanks in advance!

    Cheers!

  2. #2
    Forum Contributor jayajaya_4's Avatar
    Join Date
    05-09-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    259

    Re: Problems applying INDEX-MATCH-MATCH function on other data

    Use Vlookup function within =IFERROR(VLOOKUP(1,2,1,FALSE),”0″). This will return ‘0’. or
    Use IsNA as =ISNA(VLOOKUP(1,2,1,FALSE)). This will return “TRUE”.

    To get rid of this use “=ISERROR(1/0)”. This will return “TRUE” in case of any problem with calculation.
    Or use =IFERROR(1/0,”Error Occurred in Calculation”)

    This is one of the frequent errors you see while*using vlookup formula. The N/A error is shown when some data is missing, or inappropriate arguments are passed to the lookup functions (vlookup, hlookup etc.) of if the list is not sorted and you are trying to lookup using sort option. You can also generate a #N/A error by writing =NA() in a cell.
    How to fix #N/A error?
    Make sure you wrap the lookup functions with some error handling mechanism. For eg. if you are not sure the value you are looking is available, you can write something like =if(iserror(vlookup(…)),”not found”,vlookup(…)). This will print “not found” whenever the vlookup returns any error (including #N/A)

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

    Re: Problems applying INDEX-MATCH-MATCH function on other data

    It could be down to bad cell reference locking. I noticed that the formula in C21 wasn't locked correctly. It didn,t give the correct answer if draggeed across and down. It does now. That may have mucked up your real sheet. Try it.
    Attached Files Attached Files
    Glenn



  4. #4
    Registered User
    Join Date
    05-04-2015
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2013
    Posts
    16

    Re: Problems applying INDEX-MATCH-MATCH function on other data

    Quote Originally Posted by jayajaya_4 View Post
    Use Vlookup function within =IFERROR(VLOOKUP(1,2,1,FALSE),”0″). This will return ‘0’. or
    Use IsNA as =ISNA(VLOOKUP(1,2,1,FALSE)). This will return “TRUE”.

    To get rid of this use “=ISERROR(1/0)”. This will return “TRUE” in case of any problem with calculation.
    Or use =IFERROR(1/0,”Error Occurred in Calculation”)

    This is one of the frequent errors you see while*using vlookup formula. The N/A error is shown when some data is missing, or inappropriate arguments are passed to the lookup functions (vlookup, hlookup etc.) of if the list is not sorted and you are trying to lookup using sort option. You can also generate a #N/A error by writing =NA() in a cell.
    How to fix #N/A error?
    Make sure you wrap the lookup functions with some error handling mechanism. For eg. if you are not sure the value you are looking is available, you can write something like =if(iserror(vlookup(…)),”not found”,vlookup(…)). This will print “not found” whenever the vlookup returns any error (including #N/A)
    Ah, the point of your reply being to find out what sort of error it is? That's a proper idea, I'm not using that sort of formulas yet. I'll keep that in mind!

    @Glenn: I saw that right after I made this post, you're correct about that. However, it should mean that at least the first (correct) cell should contain a correct formula.

    Anyway, I've found the problem, it was just a regular referencing error: 'Red ' instead of 'Red'. Minor fail, just cost me 2 hours to realize .

    Well, still grateful as ever for your help guys!

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

    Re: Problems applying INDEX-MATCH-MATCH function on other data

    The dreaded extra space.... almost as annoying as the merged cell.

  6. #6
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    932

    Re: Problems applying INDEX-MATCH-MATCH function on other data

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

  7. #7
    Forum Contributor jayajaya_4's Avatar
    Join Date
    05-09-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    259

    Re: Problems applying INDEX-MATCH-MATCH function on other data

    cheers!!!

+ 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] Problems with (inverse) INDEX-MATCH Function
    By LennartB in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-07-2015, 03:53 AM
  2. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  3. Problems with Index Match Function
    By blueduds in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-23-2013, 04:25 PM
  4. Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  5. Replies: 10
    Last Post: 12-18-2012, 07:59 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