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:

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

<tbody>
</tbody>

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