Index/Match Help

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:

PHP Code:

---

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


---

Thanks for any help you can give me.

ET :dunno :help

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

PHP Code:

---

=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 ...

PHP Code:

---

=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))) 


---

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. :dunno 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 :boing

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

PHP Code:

---

=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

PHP Code:

---

=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

PHP Code:

---

= 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.

PHP Code:

---

= 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.

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

PHP Code:

---

=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

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

Quote:

---

Originally Posted by DRJ

It looks like Zack made a cut/paste error here :whip :)

---

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. :)

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 :hi: