Broken Lookup

I have a sheet that is a revolving database. I am using multiple lookup values and look up values based off that. My question is, why is this formula breaking at names that are AB-AM (Beginning)

=IF(ISERROR(HLOOKUP($A6,Definitions!$A$14:$A$426,MATCH(A$6,Definitions!$A$14:$A$426,0)+1,1)),"",HLOOKUP($A6,Definitions!$A$14:$A$426,MATCH(A$6,Definitions!$A$14:$A$426,0)+1,1))

This formula works fine on names that are AN or later in order. But it constantly breaks on names that are AM and before that.

For frame of Reference A6 is the name of the person, and the chart is a list of all names sorted alphabetically by manager.

Any Idea's please let me know

Originally Posted by JBeaucaire

You are using the final parameter of 1 in your MATCH() functions. This is a fuzzy match, not exact, and can thus have untoward results on data that isn't exactly what it appears to be.

=IF(ISERROR(HLOOKUP($A6,Definitions!$A$14:$A$426,MATCH(A$6,Definitions!$A$14:$A$426,0)+1,1)),"",HLOOKUP($A6,Definitions!$A$14:$A$426,MATCH(A$6,Definitions!$A$14:$A$426,0)+1,1))


If this isn't the issue, I'd have to see your formula failing in your workbook to see the problem. You can do what I would do, click on the cell with the formula in it and use the Evaluate Formula feature. I can't tell you where that is in Excel 2003, but it's there.

Went through the tracing of the formula on both the pages it works on, as well as the broken ones. (This formula has worked on 35 pages, and failed on 6) The names are the one consistent link between the working and non functioning names.

Through the first part is recognizing the name, even the match, and row number of the match. But it is not defining the result as False even though it is finding the data.

Figured out a way to work around the issue, thx for the idea's