Index Match Error

The formula that I have works when the match is a two digit number, but not for a one digit number. Can anyone think of any reason why?

=IF(ISERROR(INDEX('[Dept Class - FY 2007 Feb.xls]345'!$A$11:$G$2000,MATCH($N$95&$A108,'[Dept Class - FY 2007 Feb.xls]345'!$A$11:$A$2000&'[Dept Class - FY 2007 Feb.xls]345'!$B$11:$B$2000,0),4,1)),0,INDEX('[Dept Class - FY 2007 Feb.xls]345'!$A$11:$G$2000,MATCH($N$95&$A108,'[Dept Class - FY 2007 Feb.xls]345'!$A$11:$A$2000&'[Dept Class - FY 2007 Feb.xls]345'!$B$11:$B$2000,0),4,1))

A B C D
55 8 96 780
55 9 140
55 10 390
55 11 745 2430

The problem is matching on column B, returning column D. $N$95 is 55, and $A108 is the value of column B. This formula will return 2430, but not 780.

Please post formula and some sample data.

Without going into detail on the formula, it looks like it might be an array formula since your are combining 2 columns to match...

Have you confirmed the formula with CTRL+SHIFT+ENTER not just Enter...this will produce {} brackets automatically around the formula...

Originally Posted by dfab100

It is an array formula. When copying it to this site I didn't manually readd the brackets. Any other suggestions??

and there are no extraneous spaces in column B (e.g. "8 ")?

Maybe you can attach the sheet?

Here's another version of your formula that should be more robust.

Try it....

Please Login or Register  to view this content.

If it gives you the same results, then the data is not matching up, meaning they are not exactly the same in both the lookup values vs. the lookup table