Index/Match Function not working for some values

Alex.Charles

I currently have a reference sheet that calculates total hours used, which is calculating everything accurately. When I pull the total hours used into the main sheet, it will work for some, but not for others. For some, it pulls the information accurately. For others, it will pull another number that doesn't match the total calculation accurately. For a few, it will say #NO MATCH. I have tried to delete out and re-enter the formula, double-checked the employee numbers being used across accurate and inaccurate lines, and variations of adjusting parentheses with no luck. The index formula is below.

=INDEX({MWN Ops Attendance Sick Time Calc: Total Hours}, (MATCH([Employee Number]@row, {MWN Ops Attendance Sick Time Calc: Employee #})))

Kerry St. Thomas

The first thing that popped into my mind seeing this is that the source sheet isn't alphabetized perfectly. There's a third clause to MATCH that might help. the ,0 is listed as an OPTIONAL, but …I recommend exercising that option.

=INDEX({What you want to return}, MATCH([Lookup Item on the current row]@row, {Lookup Column},0))
or using your ranges
=INDEX({MWN Ops Attendance Sick Time Calc: Total Hours}, MATCH([Employee Number]@row, {MWN Ops Attendance Sick Time Calc: Employee #},0))

As a post-script, I suggest you pay attention to your Reference naming convention - I've bumped against an UNPUBLISHED limitation of max characters for Pivot. (When I pass that limit, there's no error; things just…break.) I wouldn't be surprised if a similar issue occurs with named References. If you can abbreviate ("Attend Sick Calc: Tot Hrs" for example?) or shorten, you might save yourself headaches down the line.

Kerry St. Thomas

The first thing that popped into my mind seeing this is that the source sheet isn't alphabetized perfectly. There's a third clause to MATCH that might help. the ,0 is listed as an OPTIONAL, but …I recommend exercising that option.

=INDEX({What you want to return}, MATCH([Lookup Item on the current row]@row, {Lookup Column},0))
or using your ranges
=INDEX({MWN Ops Attendance Sick Time Calc: Total Hours}, MATCH([Employee Number]@row, {MWN Ops Attendance Sick Time Calc: Employee #},0))

As a post-script, I suggest you pay attention to your Reference naming convention - I've bumped against an UNPUBLISHED limitation of max characters for Pivot. (When I pass that limit, there's no error; things just…break.) I wouldn't be surprised if a similar issue occurs with named References. If you can abbreviate ("Attend Sick Calc: Tot Hrs" for example?) or shorten, you might save yourself headaches down the line.