Index/Match

[Noz2k]

What I'm trying to do is use a value in column J of my sheet to find the matching value in column A of sheet2, and return the value in the corresponding row of column N.

Here is what I am trying

=INDEX(Sheet2!$N$3:$N$20000,MATCH($J2,Sheet2!$A$3:$A$20000,0))

*entered as an array.

Where am I going wrong?

 

[VoG]

That formula looks like it should work although it should not need to be entered as an array formula.

Wrong result or error or what?

 

[Noz2k]

It gives me N/A on every row.

I've used the Find function on sheet2 and those values are definitely there in column A

 

[JackDanIce]

Do you have your autocalculation turned on or off? Try pressing F9 to see if the values update

 

[VoG]

If you have text in J2 and numbers in column A of sheet2 (or the other way round) you would get #N/A.

 

[Noz2k]

Still the same result.

I'm not sure if there is a difference in formatting perhaps? They both say 'general' though.

However I tried a little test on the first value based on where the find function showed it to be in sheet2

=IF(J2=Sheet2!A220, Sheet2!N220,"")

and it returns ""

So there is definitely a mismatch somewhere

 

[VoG]

What do

=ISNUMBER(Sheet2!A220)

and

=ISNUMBER(J2)

return?

 

[Noz2k]

Formatting both as number as still no change.

I'm wondering if maybe there is a invisible value at the end or beginning? I think I remember hearing this could be the case?

 

[Noz2k]

Isnumber220 returns false

isnumberj2 returns true

So there's the mismatch. How do I match them?

 

[VoG]

Try this http://www.contextures.com/xlFunctions02.html#trouble