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!

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)

Originally Posted by jayajaya_4

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!

Formula:

Please Login or Register  to view this content.

cheers!!!