Index/Match Help

[ETracker]

Hi everyone,

Attached I have attached a sample of the Index/Match formula that I am using. The formula is working great and does a great job, but I am getting #N/A in some of the cells because there is not a code for that row. Please note that I have used named ranges and the data sheet will be a pivot table and will not have all of the required codes.

I need to know how I can change or add to the formula to get rid of the #N/A in the cells that does not have a code on the data sheet.

The formula that I am using is:

=INDEX(Data1,MATCH($A3,Code,0),MATCH(B$2,Contract,0)) 

Thanks for any help you can give me.

ET

[Zack Barresse]

One way, the easiest way to do it (imho), is to download/install the morefunc.xll add-in. Then use a formula like ...

=IF(ISNA(SETV(INDEX(Data1,MATCH($A3,Code,0),MATCH(B$2,Contract, 0)))),"ERROR",GETV()) 

Then you have the ever present, albeit not efficient ...

=IF(ISNA(INDEX(Data1,MATCH($A3,Code,0),MATCH(B$2,Contract, 0))),"ERROR",=INDEX(Data1,MATCH($A3,Code,0),MATCH(B$2,Contract, 0))) 

[ETracker]

Thanks for the quick response firefytr,

I have used the non-efficient or 2nd method tonight and at first I thought it worked great, but it did not function correctly. Not sure why, It says that the formula contains an error. I like this method if I can get it to work, this will be OK for what I am doing at this time.

I would like to know more about the morefunc.xll add-in. Where can I get it and how do I download and install it.

Again thank you for the quick response with a great answer.

Thank You

ET

[Jacob Hilderbrand]

It looks like Zack made a cut/paste error here . Notice there are two equal signs (=). Try this:

=IF(ISNA(INDEX(Data1,MATCH($A3,Code,0),MATCH(B$2,Contract, 0))),"ERROR",INDEX(Data1,MATCH($A3,Code,0),MATCH(B$2,Contract, 0))) 

--------------------------------------------------------------------------

For a general formula you can use this for NA Errors

=If(ISNA(Formula),"Error",Formula) 

This is really not efficient though. Excel has to evaluate the "Formula" twice and if we have thousands of these complex formulas it could impact the speed of your spreadsheet.

--------------------------------------------------------------------------

If it is possible use two cells for each formula. Basically setup two tables or columns one for the formula and one to check if it is NA or some other error.

For example:

Let's say your original formula is in Z1

= INDEX(Data1,MATCH($A3,Code,0),MATCH(B$2,Contract, 0)) 

Z1 can be hidden so you don't even see it.

In A1 you can put this.

= If(ISNA(Z1),"Error",Z1) 

--------------------------------------------------------------------------

morefunc is attached. Just unzip it and follow the Readme.txt instructions to install it. But just make sure you understand that any functions you use from that add-in will only work on PCs that have the add-in.

So if you use morefunc functions and send the spreadsheet to someone that doesn't have the add-in, it won't work.

[tommy bak]

Hi
In this case where the data is from a pivottable, it might be easier to use GETPIVOTDATA

This formula to be placed in B3

=IF(ISERROR(GETPIVOTDATA(Data!$A$3;B$2 & " " & $A3));"";GETPIVOTDATA(Data!A3;B$2 & " " & $A3)) 

[EDIT] : This is how GETPIVOTDATA is used in excel 97 and 2000. This old syntax works in XP, but XP's syntax doesn't work in 97/2000, so I would choose the old syntax for compability.

BR
Tommy Bak

[ETracker]

Thanks for the repley DRJ and Tommy.

I will try both samples and let you know how it works. Also thanks for the Morefunc file. I will load it to see if it is what I need to be more efficient.

Again, Thank you.

ET

[Zack Barresse]

It looks like Zack made a cut/paste error here

Thanks Jake! That was kind of a bonehead error, eh?! LOL :rofl Sorry 'bout the confusion there.

And I personally like to use the morefunc.xll add-in, it makes your functions quite efficient.

[ETracker]

Thanks firefytr, DRJ, and tommy for all of your help. Also thanks DRJ for the morefunc.xll add-in.

I have used the formula without the add-in at this time and it is working great. I will try adding the add-in and using the morefunc.xll to see how much more efficient it will be.

Again thanks for all of the help.

ET