Cell Formatting When Using Index Match

[kxellis26]

Greetings -

I have an issue that my little brain can't seem to wrap itself around. I put an index match formula in to pull hours data for employees, based on either their e-mail address or employee ID. The e-mail address pulls back the data I need with no issues, but the employee ID (ADP) information doesn't work. I figured it was because the cell needed to be formatted as a number, but it's still not working. When I just type out the employee ID in another cell, the formula works like a sweet, sweet dream. Anyone got any ideas?

ADP vs Five 9 Variance Report.xlsx
	A	B	C	D	E	F	G
1
2		Name	E-Mail	ID
3		Spencer, Alysia J	aspencer@risebroadband.com	118005533		118005533
4
5		Date	ADP	Five9	Five9 Hours	Variance
6		1-Jun	8.15	8:04:03	8.07	0.08
7		2-Jun	8.38	8:10:59	8.18	0.20
8		3-Jun		0:00:00	0.00
9		4-Jun		0:00:00	0.00
10		5-Jun	8.43	8:19:11	8.32	0.11
11		6-Jun	3.00	5:07:21	5.12	-2.12
12		7-Jun	8.10	8:06:25	8.11	-0.01
13		8-Jun	8.18	8:12:45	8.21	-0.03
14		9-Jun	8.00	7:54:10	7.90	0.10
15		10-Jun		0:00:00	0.00
16		11-Jun		0:00:00	0.00
17		12-Jun	8.13	7:54:54	7.92	0.22
18		13-Jun	8.12	8:03:39	8.06	0.06
19		14-Jun	8.05	8:03:25	8.06	-0.01
20		15-Jun	8.12	8:02:15	8.04	0.08
21		16-Jun	7.58	8:08:06	8.14	-0.56
22		17-Jun		0:00:00	0.00
23		18-Jun		0:00:00	0.00
24		19-Jun	8.22	8:10:22	8.17	0.05
25		20-Jun	8.28	9:14:51	9.25	-0.97
26		21-Jun	8.08	7:44:50	7.75	0.33
27		22-Jun	1.00	5:32:26	5.54	-4.54
28		23-Jun	8.12	8:53:34	8.89	-0.77
29		24-Jun		0:00:00	0.00
30		25-Jun		0:00:00	0.00
31		26-Jun	8.60	8:28:47	8.48	0.12
32		27-Jun	6.72	7:18:44	7.31	-0.59
33		28-Jun	4.05	4:01:28	4.02	0.03
34		29-Jun	8.33	8:05:42	8.10	0.23
35		30-Jun	7.98	8:09:15	8.15	-0.17
36		1-Jul		0:00:00	0.00
37
Search

Cell Formulas
Range	Formula
C3	C3	=INDEX(Roster!K:K,MATCH(Search!B3,Roster!D:D,0))
D3	D3	=INDEX(Roster!J:J,MATCH(Search!B3,Roster!D:D,0))
C6:C36	C6	=IFERROR(INDEX(ADP!N:N,MATCH(1,(ADP!E:E=Search!$I$5)*(ADP!F:F=Search!B6),0)),"")
D6:D36	D6	=IFERROR(INDEX(Five9!C:C,MATCH(1,(Five9!H:H=$C$3)*(Five9!A:A=B6),0)),"")
E6:E36	E6	=IFERROR((D6*24),"")
F6:F36	F6	=IFERROR((C6-E6),"")

Named Ranges
Name	Refers To	Cells
Five9!_FilterDatabase	=Five9!$A$1:$H$124578	D6:D36

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=Roster!$D$2:$D$214

 

[Joe4]

When matching on values, both values must be the same data type.
If one is entered as a valid number, but the other number is actually entered as text, it will not work.

You can easily verify this by checking each value with the ISNUMBER function, i.e.
=ISNUMBER(D3)
=ISNUMBER(F3)

If one returns TRUE and the other FALSE, then you have different data types. You must convert one or the other so they are all the same.

 

[kxellis26]

When matching on values, both values must be the same data type.
If one is entered as a valid number, but the other number is actually entered as text, it will not work.

You can easily verify this by checking each value with the ISNUMBER function, i.e.
=ISNUMBER(D3)
=ISNUMBER(F3)

If one returns TRUE and the other FALSE, then you have different data types. You must convert one or the other so they are all the same.

Yeah, that's what I was thinking, but even when I change the cell to a number, it still doesn't work. When I check "=isnumber" it comes back as false. The data from the tab it's pulling from is also formatted as a number. It's making me crazy.

	Name	E-Mail	ID	Type ID Here
	Narcisse, Walter Patrick	wnarcisse@risebroadband.com	118005464	118005464	FALSE	TRUE

 

[Joe4]

Yeah, that's what I was thinking, but even when I change the cell to a number, it still doesn't work. When I check "=isnumber" it comes back as false. The data from the tab it's pulling from is also formatted as a number. It's making me crazy.

	Name	E-Mail	ID	Type ID Here
	Narcisse, Walter Patrick	wnarcisse@risebroadband.com	118005464	118005464	FALSE	TRUE

Changing the format of a cell has no impact on the data type and how the value was entered.
Formatting only affects numeric entries.
So changing the format of a cell that has data that was entered as text will NOT fix it.
You need to change the format to a non-text format (General, Number, etc) and THEN you must re-enter the data!
If you have a lot of cells to do, you can do it a column at a time using Text to Columns.

 

[kxellis26]

Changing the format of a cell has no impact on the data type and how the value was entered.
Formatting only affects numeric entries.
So changing the format of a cell that has data that was entered as text will NOT fix it.
You need to change the format to a non-text format (General, Number, etc) and THEN you must re-enter the data!
If you have a lot of cells to do, you can do it a column at a time using Text to Columns.

Got it. So if the source data I'm pasting is formatted as number when I paste, it should work? I pulled it down as CSV, so I'm going to try pulling it as XLS and format it as numbers.

Hopefully I didn't miss the point here. I am not near my computer, but will try ASAP.

Thank your for the assistance.