VLOOKUP Fails Sometimes

[goss]

Hi all,

Using Excel 2010.

My VLOOKUP fails on 2 specific GL Codes, but work on all others.
I sorted my reference array.
I ran a deleted duplicate macro to ensure there are no duplicate entries, there weren't.
I review the criteria versus the ref array to make sure they are the same, they are
I searched the ref array for the values to make sure they are there - they are.

I'm stumped.
Ideas?

 

[goss]

Thanks Universo,

Do you mean like this?

=EXACT(Data!C136,Ess!A3)
=EXACT(Ess!A3,Data!C136)

Both compare 60110020 on one sheet versus another sheet
Both return TRUE

thx
w

 

[Universo Excel]

Yeah, that's precisely what i was suggesting....

Hmmm... Have you tried using =MATCH()?

To at least confirm if it is identifying your numbers...

 

[Aladin Akyurek]

Hi all,

Using Excel 2010.

My VLOOKUP fails on 2 specific GL Codes, but work on all others.
I sorted my reference array.
I ran a deleted duplicate macro to ensure there are no duplicate entries, there weren't.
I review the criteria versus the ref array to make sure they are the same, they are
I searched the ref array for the values to make sure they are there - they are.

I'm stumped.
Ideas?

goss

The Ess sheet entries are not clean:

=SUBSTITUTE(A1057,CHAR(160),"")+0

yields a matchable entry/number. CHAR(160) is often seen in the data copied/downloaded from a web page.

The TrimAll macro would clean the Ess sheet for you.

By the way, it looks like the table area on Ess is sorted in ascending order. If this is true, you can invoke much faster:

=IF(ISNUMBER(C2),IF(VLOOKUP(C2,tblAccounts,1,1)=C2,VLOOKUP(C2,tblAccounts,2,1),""),"")

in lieu of

=VLOOKUP(C2,tblAccounts,2,FALSE)

 

[goss]

Thanks Aladin,

I selected eveything on both sheets, re-ran TrimAll Macro. Still did not work. I've been using the TrimAll Macro for many years, first time I have ever seen it fail. Your formula definitely did the job though - thanks!

It does seem a little odd that the I tested the length of GL Number which returned 8 as expected. If the Char(160) is present, shouldn't the length report as 9?

I tested, on a throw-away, I entered =61621200&CHAR(160) which does return a length of 9.

Also a little odd in the EXACT() test below returned TRUE?

The data on Ess was extracted from a string which in turn was extracted from an Essbase Cube. I'll take another run at my formula that extracted a portion of the string from the Cube.

Thx
w