LookUp/Match/Index Not Working

[mulholm]

I have the following formula:

=(LOOKUP(B3,January!D:D,January!J:J))

B3 = "Agent 3"

The Table i am trying to look form is the following:

[TABLE="width: 909"]
<tbody>[TR]
[TD][TABLE="width: 909"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]CSM[/TD]
[TD]Manager[/TD]
[TD]Agent[/TD]
[TD]Conformance %[/TD]
[TD]Adherence %[/TD]
[TD]Inbound Calls[/TD]
[TD]Ave Inb Talk[/TD]
[TD]Ave Inb Hold[/TD]
[TD]Avg Inb Wrap[/TD]
[TD]Inb AHT[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]Agent 1[/TD]
[TD="align: right"]99.75%[/TD]
[TD="align: right"]97.0%[/TD]
[TD="align: right"]7,740[/TD]
[TD="align: right"]360[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]525[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]Agent 2[/TD]
[TD="align: right"]104.26%[/TD]
[TD="align: right"]99.7%[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]482[/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]208[/TD]
[TD="align: right"]690[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]Agent 3[/TD]
[TD="align: right"]100.32%[/TD]
[TD="align: right"]97.8%[/TD]
[TD="align: right"]841[/TD]
[TD="align: right"]242[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]129[/TD]
[TD="align: right"]371[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The formula should return "129" as this is what "Agent 3" Avg Inb Wrap is, however when i run the formula it just returns "Avg Inb Wrap".

Any ideas how this is?

 

[SpillerBD]

The INDEX(..MATCH()) would be better as you set the Match Type to Exact.
LOOKUP requires the data to be sorted and may still provide incorrect results.

 

[mulholm]

I tried index/match and it returns the same value .

Instead of matching row 4 it matches row 1

 

[Fluff]

Try it like
=INDEX(January!A2:K4,MATCH(B3,January!D2:D4,0),10)

 

[mulholm]

Not working either

I doesn't seem to be picking up the "Match" part of it.

When I just index it, it worked but row will change so can't use only index

 

[Fluff]

Works for me

Excel 2013/2016
	B	C
3	Agent 3	129
Sheet1

Cell Formulas
Range	Formula
C3	=INDEX(January!A2:K4,MATCH(B3,January!D2:D4,0),10)

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K
1	Location	CSM	Manager	Agent	Conformance %	Adherence %	Inbound Calls	Ave Inb Talk	Ave Inb Hold	Avg Inb Wrap	Inb AHT
2	X	X	X	Agent 1	99.75%	97.00%	7,740	360	91	165	525
3	X	X	X	Agent 2	104.26%	99.70%	68	482	116	208	690
4	X	X	X	Agent 3	100.32%	97.80%	841	242	38	129	371
January

Are you sure that the contents of B3 are EXACTLY the same as in the table

 

[mulholm]

Yeah I honestly don't know why it isn't working. I will try again and see if I can work it out.

Thanks for your help though, I will use it as a reference point.

 

[Fluff]

If you do
=LEN(B3)
=LEN(January!D4)
Where D4 is the cell that should match B3, do you get the same result?

 

[mulholm]

Managed to get it.

Whoever made the spreadsheet has a double space inbetween agent and1

 

[Fluff]

Glad you sorted it & thanks for the feedback