Forum Discussion
INDEX MATCH formula showing as #N/A - but only in some instances; same info
I am using this formula to pull a date from one sheet to another, by matching the employee ID(s).
=INDEX(HireDate!$P$8:$P$17000,MATCH(D9,HireDate!$A$8:$A$17000,0))
99% of my formulas result in the correct information. However, I have a handful that are showing as #N/A.
WHY.
It has to be something simple. If I do CTRL F and search the employee ID, it shows them in the correct spot on the corresponding tab. There are no extra spaces, checked that. I pasted all the info as text. I also tried a second way, and pulled hire date from an entirely different source/sheet and still show an error. Both with the same formula.
3 Replies
Check why some of the values in the first column in your screenshot are left-aligned and others centered...
What would cause them to left align? I checked the standard alignment; they are formatted for number. The only ones that are left align are those that have errors..
I also checked Data> txt to columns.
What happens if you select that column, then:
- On the Data tab of the ribbon, click Text to Columns.
- Click Finish without changing any settings.
