Index Match is pulling percentages as zeros

[kxellis26]

Greetings -

I built a sheet today where I am pulling percentages from one tab into another with INDEX MATCH. Unfortunately, instead of pulling percentages back, it's pulling zeros. I have double and triple checked the formula and it's solid. I tried formatting the cells, of course, but no luck.

I am sure it's an easy fix and the 12 hours I've been working today isn't helping. The Google machine is of no help on this one. Any ideas?

 

[Kamolga]

Hi,

Could you share screenshots of the formula-ranges? Is the matching value in the same format than matching lookup (is it text? Is match type=0?) => when matching values, it is usual good to match(value(123),lookup, and chose -1,0,1 wisely because of decimals

 

[etaf]

an example would help here , either XL2BB or on a share
what did you format the cells to
what is the actual % value you are bringing over - is it under 1% which would be 0.01 and rounding if no decimals, may indicate 0
maybe change to show 4 decimal places

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[kxellis26]

Agent	Supervisor	Start Date	Department	Learner Hours	Cambium	VoIP	Adv Tools	Billing	Upgrades	Score
Baird, David	Dubon, Kevin	4/16/2019	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y
Baker, Joseph	Hardon, Ryan	6/2/2021	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0%
Bennett, Joseph	McCabe, Anthony	4/16/2019	Representative, Technical Support II	0						#N/A
Bowes, Alexandra	Hardon, Ryan	11/5/2018	Representative, Technical Support II	10.5	Y	Y	Y	Y	Y	0.00%
Brakke, Bryson	Hardon, Ryan	4/16/2019	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
Briceno, Austin L	Hardon, Ryan	1/26/2022	Representative, Technical Support I	9.25	Y	Y	Y	N	Y	0.00%
Cano, Arthur	Hardon, Ryan	7/28/2021	Representative, Technical Support II	10.5	Y	Y	Y	Y	Y	0.00%
Cantrell, Charles	Dubon, Kevin	2/17/2021	Representative, Technical Support II	10.5	Y	Y	Y	Y	Y	0.00%
Casanova, Erica V	Hardon, Ryan	2/17/2021	Representative, Technical Support II	10.5	Y	Y	Y	Y	Y	0.00%
Chesnick, Kyle	Hardon, Ryan	8/17/2020	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
Christensen, Nicholas	Small, Brandon	5/7/2019	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
Crowley, Evan	Harrington, Glenn	10/13/2021	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
Curtis, Tyler	McCabe, Anthony	4/13/2022	Representative, Technical Support II	0						#N/A
Davila, Mercedes	Small, Brandon	5/7/2019	Representative, Technical Support I	9.25	Y	Y	Y	Y	Y	0.00%
Delon, Anthony	Hardon, Ryan	9/6/2018	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
Derringer, Ryan	Small, Brandon	6/2/2021	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
DuBard, Chase	Small, Brandon	10/31/2016	Representative, Technical Support II	9.25	N	Y	Y	Y	N	0.00%
Dunn, Cody	Hardon, Ryan	9/20/2018	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%

Hughes, Samantha	Hardon, Ryan	5/6/22	L2 Assessment	1.25	94%
Irwin, Joy	Harrington, Glenn	5/10/22	L2 Assessment	1.25	85%
Kachel, Gregg S	Harrington, Glenn	5/19/22	L2 Assessment	1.25	85%
Kunz, Maria	Hardon, Ryan	5/12/22	L2 Assessment	1.25	87%
Lennemann, Kerry	Harrington, Glenn	5/11/22	L2 Assessment	1.25	89%
MacLean, Thomas	Dubon, Kevin	4/26/22	L2 Assessment	1.25	82%
Madigan, Aidan R	Hardon, Ryan	4/29/22	L2 Assessment	1.25	92%
Miller, Seth Tyler James	Hardon, Ryan	5/10/22	L2 Assessment	1.25	83%
Ornelas, Cathline	Small, Brandon	5/10/22	L2 Assessment	1.25	81%
Parsons, Christian	Hardon, Ryan	5/11/22	L2 Assessment	1.25	94%
Rattarree, Logan	Dubon, Kevin	5/6/22	L2 Assessment	1.25	91%
Romano, Scott	Harrington, Glenn	5/11/22	L2 Assessment	1.25	79%
Roybal, Andres	Small, Brandon	5/10/22	L2 Assessment	1.25	55%
Schade, Lynda	Small, Brandon	5/10/22	L2 Assessment	1.25	83%
Shadid, James	Hardon, Ryan	5/11/22	L2 Assessment	1.25	91%
Sherwood, Donald	Harrington, Glenn	5/11/22	L2 Assessment	1.25	89%
Sott, Erin	Dubon, Kevin	5/15/22	L2 Assessment	1.25	94%
Sparks, Dianne	Harrington, Glenn	5/19/22	L2 Assessment	1.25	74%
Wardin, Derek	Small, Brandon	5/10/22	L2 Assessment	1.25	72%
Weller, Julia	Harrington, Glenn	5/19/22	L2 Assessment	1.25	79%

 

[RoryA]

Can you do the mini-sheet option to include the formulas?

 

[kxellis26]

My bad!

Certification Tracker - NEW.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2	Agent	Supervisor	Start Date	Department	Learner Hours	Cambium	VoIP	Adv Tools	Billing	Upgrades	Score
3	Baird, David	Dubon, Kevin	4/16/2019	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y
4	Baker, Joseph	Hardon, Ryan	6/2/2021	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0%
5	Bennett, Joseph	McCabe, Anthony	4/16/2019	Representative, Technical Support II	0						#N/A
6	Bowes, Alexandra	Hardon, Ryan	11/5/2018	Representative, Technical Support II	10.5	Y	Y	Y	Y	Y	0.00%
7	Brakke, Bryson	Hardon, Ryan	4/16/2019	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
8	Briceno, Austin L	Hardon, Ryan	1/26/2022	Representative, Technical Support I	9.25	Y	Y	Y	N	Y	0.00%
9	Cano, Arthur	Hardon, Ryan	7/28/2021	Representative, Technical Support II	10.5	Y	Y	Y	Y	Y	0.00%
10	Cantrell, Charles	Dubon, Kevin	2/17/2021	Representative, Technical Support II	10.5	Y	Y	Y	Y	Y	0.00%
11	Casanova, Erica V	Hardon, Ryan	2/17/2021	Representative, Technical Support II	10.5	Y	Y	Y	Y	Y	0.00%
12	Chesnick, Kyle	Hardon, Ryan	8/17/2020	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
13	Christensen, Nicholas	Small, Brandon	5/7/2019	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
14	Crowley, Evan	Harrington, Glenn	10/13/2021	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
15	Curtis, Tyler	McCabe, Anthony	4/13/2022	Representative, Technical Support II	0						#N/A
16	Davila, Mercedes	Small, Brandon	5/7/2019	Representative, Technical Support I	9.25	Y	Y	Y	Y	Y	0.00%
17	Delon, Anthony	Hardon, Ryan	9/6/2018	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
18	Derringer, Ryan	Small, Brandon	6/2/2021	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
19	DuBard, Chase	Small, Brandon	10/31/2016	Representative, Technical Support II	9.25	N	Y	Y	Y	N	0.00%
20	Dunn, Cody	Hardon, Ryan	9/20/2018	Representative, Technical Support I	10.5	Y	Y	Y	Y	Y	0.00%
Learner Roll-Up

Cell Formulas
Range	Formula
C3:C20	C3	=INDEX(Roster!G:G,MATCH('Learner Roll-Up'!B3,Roster!D:D,0))
D3	D3	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B3,Roster!D:D,0))
E3	E3	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B3,Roster!D:D,0))
F3:F20	F3	=SUMIF('Training Log'!B:B,'Learner Roll-Up'!B3,'Training Log'!F:F)
G3:G20	G3	=IFERROR(INDEX('Training Log'!I:I,MATCH(1,(B3='Training Log'!B:B)*(G$2='Training Log'!E:E),0)),"")
H3:H20	H3	=IFERROR(INDEX('Training Log'!I:I,MATCH(1,(B3='Training Log'!B:B)*(H$2='Training Log'!E:E),0)),"")
I3:I20	I3	=IFERROR(INDEX('Training Log'!I:I,MATCH(1,(B3='Training Log'!B:B)*(I$2='Training Log'!E:E),0)),"")
J3:J20	J3	=IFERROR(INDEX('Training Log'!I:I,MATCH(1,(B3='Training Log'!B:B)*(J$2='Training Log'!E:E),0)),"")
K3:K20	K3	=IFERROR(INDEX('Training Log'!I:I,MATCH(1,(B3='Training Log'!B:B)*(G$2='Training Log'!E:E),0)),"")
D4:D8	D4	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B14,Roster!D:D,0))
E4	E4	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B31,Roster!D:D,0))
L4:L20	L4	=INDEX('Training Log'!G:G,MATCH('Learner Roll-Up'!B4,'Training Log'!B:B,0))
E5	E5	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B6,Roster!D:D,0))
E6:E8	E6	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B17,Roster!D:D,0))
D9	D9	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B4,Roster!D:D,0))
E9	E9	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B4,Roster!D:D,0))
D10:D11	D10	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B19,Roster!D:D,0))
E10	E10	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B20,Roster!D:D,0))
E11	E11	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B7,Roster!D:D,0))
D12	D12	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B45,Roster!D:D,0))
E12	E12	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B21,Roster!D:D,0))
D13	D13	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B33,Roster!D:D,0))
E13	E13	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B36,Roster!D:D,0))
D14	D14	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B46,Roster!D:D,0))
E14	E14	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B22,Roster!D:D,0))
D15	D15	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B21,Roster!D:D,0))
E15	E15	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B8,Roster!D:D,0))
D16:D17	D16	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B47,Roster!D:D,0))
E16:E17	E16	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B23,Roster!D:D,0))
D18	D18	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B22,Roster!D:D,0))
E18	E18	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B21,Roster!D:D,0))
D19	D19	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B34,Roster!D:D,0))
E19	E19	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B37,Roster!D:D,0))
D20	D20	=INDEX(Roster!A:A,MATCH('Learner Roll-Up'!B49,Roster!D:D,0))
E20	E20	=INDEX(Roster!F:F,MATCH('Learner Roll-Up'!B25,Roster!D:D,0))

Named Ranges
Name	Refers To	Cells
Roster!_FilterDatabase	=Roster!$A$2:$K$135	D3:D20

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:M60,O3:R60	Cell Value	="Y"	text	NO
G3:M60,O3:R60	Cell Value	="N"	text	NO

Certification Tracker - NEW.xlsx
	B	C	D	E	F	G
165	Hughes, Samantha	Hardon, Ryan	5/6/22	L2 Assessment	1.25	94%
166	Irwin, Joy	Harrington, Glenn	5/10/22	L2 Assessment	1.25	85%
167	Kachel, Gregg S	Harrington, Glenn	5/19/22	L2 Assessment	1.25	85%
168	Kunz, Maria	Hardon, Ryan	5/12/22	L2 Assessment	1.25	87%
169	Lennemann, Kerry	Harrington, Glenn	5/11/22	L2 Assessment	1.25	89%
170	MacLean, Thomas	Dubon, Kevin	4/26/22	L2 Assessment	1.25	82%
171	Madigan, Aidan R	Hardon, Ryan	4/29/22	L2 Assessment	1.25	92%
172	Miller, Seth Tyler James	Hardon, Ryan	5/10/22	L2 Assessment	1.25	83%
173	Ornelas, Cathline	Small, Brandon	5/10/22	L2 Assessment	1.25	81%
174	Parsons, Christian	Hardon, Ryan	5/11/22	L2 Assessment	1.25	94%
175	Rattarree, Logan	Dubon, Kevin	5/6/22	L2 Assessment	1.25	91%
176	Romano, Scott	Harrington, Glenn	5/11/22	L2 Assessment	1.25	79%
177	Roybal, Andres	Small, Brandon	5/10/22	L2 Assessment	1.25	55%
178	Schade, Lynda	Small, Brandon	5/10/22	L2 Assessment	1.25	83%
179	Shadid, James	Hardon, Ryan	5/11/22	L2 Assessment	1.25	91%
180	Sherwood, Donald	Harrington, Glenn	5/11/22	L2 Assessment	1.25	89%
181	Sott, Erin	Dubon, Kevin	5/15/22	L2 Assessment	1.25	94%
182	Sparks, Dianne	Harrington, Glenn	5/19/22	L2 Assessment	1.25	74%
183	Wardin, Derek	Small, Brandon	5/10/22	L2 Assessment	1.25	72%
184	Weller, Julia	Harrington, Glenn	5/19/22	L2 Assessment	1.25	79%
Training Log

Cell Formulas
Range	Formula
C165:C184	C165	=IFERROR(INDEX(Roster!G:G,MATCH(B165,Roster!D:D,0)),"")
F165:F184	F165	=IFERROR(VLOOKUP([@Module],Formulas!$H$4:$I$13,2,FALSE),"")

Cells with Data Validation
Cell	Allow	Criteria
E165:E184	List	=Formulas!$B$4:$B$13
B165:B184	List	='Learner Roll-Up'!$B$3:$B$60

 

[RoryA]

Unfortunately, none of the names you have posted match the log sheet, so all the formulas are #N/A currently.

However, amending a name to match, then pulls the percentage through correctly.

Do you by any chance have a circular reference error mentioned in the status bar?

 

[etaf]

=INDEX('Training Log'!G:G,MATCH('Learner Roll-Up'!B4,'Training Log'!B:B,0))
seems to be correct formula

are you matching with agent or supervisor ?

Agent B, which you have
Supervisor C - which seems to be the matching names

 

[kxellis26]

Unfortunately, none of the names you have posted match the log sheet, so all the formulas are #N/A currently.

However, amending a name to match, then pulls the percentage through correctly.

Do you by any chance have a circular reference error mentioned in the status bar?

Oh, haha. I didn't even notice that. No circular references though.

Certification Tracker - NEW.xlsx
	B	C	D	E	F	G	H	I	J	K
2	Agent	Supervisor	Date	Module	Hours	L2 AST	L3 AST	Pass Y/N	Facilitator	Facilitator Hours
149	Baird, David	Dubon, Kevin	5/6/22	L2 Assessment	1.25	89%		Y
150	Baker, Joseph	Hardon, Ryan	4/26/22	L2 Assessment	1.25	96%		Y
151	Bowes, Alexandra	Hardon, Ryan	5/9/22	L2 Assessment	1.25	85%		Y
152	Cano, Arthur	Hardon, Ryan	4/28/22	L2 Assessment	1.25	96%		Y
153	Cantrell, Charles	Dubon, Kevin	5/10/22	L2 Assessment	1.25	89%		y
154	Casanova, Erica V	Hardon, Ryan	5/13/22	L2 Assessment	1.25	91%		Y
155	Chesnick, Kyle	Hardon, Ryan	4/27/22	L2 Assessment	1.25	94%		Y
156	Christensen, Nicholas	Small, Brandon	4/27/22	L2 Assessment	1.25	86%		Y
157	Crowley, Evan	Harrington, Glenn	4/26/22	L2 Assessment	1.25	70%		N
158	Delon, Anthony	Hardon, Ryan	5/11/22	L2 Assessment	1.25	91%		Y
159	Derringer, Ryan	Small, Brandon	5/9/22	L2 Assessment	1.25	87%		Y
160	Dunn, Cody	Hardon, Ryan	4/28/22	L2 Assessment	1.25	90%		Y
161	Frank, Christopher	Dubon, Kevin	5/9/22	L2 Assessment	1.25	83%		N
162	Gertge, Josiah	Hardon, Ryan	5/9/22	L2 Assessment	1.25	85%		Y
163	Harding, Jacob	Hardon, Ryan	5/10/22	L2 Assessment	1.25	96%		Y
164	Holford, Charles	Small, Brandon	5/13/21	L2 Assessment	1.25	72%		N
165	Hughes, Samantha	Hardon, Ryan	5/6/22	L2 Assessment	1.25	94%		Y
166	Irwin, Joy	Harrington, Glenn	5/10/22	L2 Assessment	1.25	85%		Y
167	Kachel, Gregg S	Harrington, Glenn	5/19/22	L2 Assessment	1.25	85%		Y
168	Kunz, Maria	Hardon, Ryan	5/12/22	L2 Assessment	1.25	87%		Y
169	Lennemann, Kerry	Harrington, Glenn	5/11/22	L2 Assessment	1.25	89%		Y
170	MacLean, Thomas	Dubon, Kevin	4/26/22	L2 Assessment	1.25	82%		N
Training Log

Cell Formulas
Range	Formula
C149:C170	C149	=IFERROR(INDEX(Roster!G:G,MATCH(B149,Roster!D:D,0)),"")
F149:F170	F149	=IFERROR(VLOOKUP([@Module],Formulas!$H$4:$I$13,2,FALSE),"")
K149:K170	K149	=IFERROR((VLOOKUP([@Module],Formulas!$H$4:$I$8,2,FALSE)+0.5),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G149	Cell Value	="Y"	text	NO
G149	Cell Value	="N"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3:E170	List	=Formulas!$B$4:$B$13
B3:B170	List	='Learner Roll-Up'!$B$3:$B$60
I3:I1048575	List	=Formulas!$F$10:$F$12
J3:J1048574	List	=Formulas!$D$4:$D$11

 

[kxellis26]

=INDEX('Training Log'!G:G,MATCH('Learner Roll-Up'!B4,'Training Log'!B:B,0))
seems to be correct formula

are you matching with agent or supervisor ?

Agent B, which you have
Supervisor C - which seems to be the matching names

I do have it matching to the supervisor as well, but that seems to be working correctly. For the assessment scores though, it's just bringing back 0.00%. I have tried changing the formatting of the cell, but nothing works.