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?

 

[NiceGuyRitchie]

The CLEAN() and TRIM() functions are designed to remove special characters and spaces from text values.

 

[ronbuller]

hi goss,

just a suggestion, why not create a dummy set of information composed of say..5 or 10 rows x 3 columns but still use those GL codes specially the ones that dont work mixed with the ones that do work(so you don't have to disclose any classified company info) . Try it on a clean excel sheet. If the problem persist then try sending that dummy to Aladin and see how it goes

I'm interested to see the results really as I might run into the same issue in the future.

 

[snoopyhr]

look at this maybe your macro is not picking up this sort of accourance, just a thought, may not be your case.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
6		*	*	*	*	*	*	*	*	*	*	*	*
7	Lookup value	Result	*	*	Lookup array	Length	*	*	*	*	*	*	*
8	60110020	60110020	8	*	60110010	8	*	*	*	*	*	*	*
9	60110070	60110070	8	*	60110030	8	*	*	*	*	*	*	*
10	60110020	#N/A	8	*	60110020	8	Although 8 character but it starts with "'" single quote	*	*	*	*	*	*
11	60110020	#N/A	8	*	60110050	8	and the result fails in 2 occasion	*	*	*	*	*	*
12	60110020	60110020	8	*	60110060	8	*	*	*	*	*	*	*
13	*	*	*	*	60110070	8	The highlighted one matches because the lookup value starts with single quote	*	*	*	*	*	*
14	*	*	*	*	60110080	8	*	*	*	*	*	*	*
15	*	*	*	*	60110090	8	*	*	*	*	*	*	*
16	*	*	*	*	60110100	8	*	*	*	*	*	*	*
17	*	*	*	*	60110110	8	*	*	*	*	*	*	*
18	*	*	*	*	60110120	8	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*	*	*	*
Sheet

 

[Aladin Akyurek]

Thanks Aladin,

The GL number is 8 char's long such as 60110020 or 60110070.
Both of these numbers fail for 26 units involved in the VLOOKUP

However, 60110010, 60110030, 60110040 etc... all work as expected.
The ref array has over 2000 rows of GL acocunts which I extracted from our Essbase Cube

I scrubbed the extracted data and tested to ensure no non-printing char's, no spaces and isnumber. Lastly, I copied the values from the ref array to the summary sheet to ensure I was using the exact same numbers for my criteria.

Still, the two values above return errors.

thx
w

What are the results for:

=INDEX(tblAccounts,2,1)

=ISNUMBER(INDEX(tblAccounts,2,1))

=LEN(INDEX(tblAccounts,2,1))

If C2 = 60110020, then:

=C2=INDEX(tblAccounts,2,1))

 

[goss]

Thanks Aladin,

What are the results for:

=INDEX(tblAccounts,2,1)

=ISNUMBER(INDEX(tblAccounts,2,1))

=LEN(INDEX(tblAccounts,2,1))

If C2 = 60110020, then:

=C2=INDEX(tblAccounts,2,1))

=INDEX(tblAccounts,2,1)
60110010, As expected. I sorted the ref array, low to high. It is the second smallest value.

=ISNUMBER(INDEX(tblAccounts,2,1))
TRUE, As expected. The LEN() was/is 8. The contents of the cell naturally right justify.

=LEN(INDEX(tblAccounts,2,1))
8, As expected. Previous tests in a helper column =LEN(A3) return same result

=C2=INDEX(tblAccounts,2,1))
I altered slightly. The value is in Col A and 3rd row so I altered to this:
=A3=INDEX(tblAccounts,3,1). Yields TRUE as expected.

Thanks again, Aladin.
w

 

[Aladin Akyurek]

Thanks Aladin,



=INDEX(tblAccounts,2,1)
60110010, As expected. I sorted the ref array, low to high. It is the second smallest value.

=ISNUMBER(INDEX(tblAccounts,2,1))
TRUE, As expected. The LEN() was/is 8. The contents of the cell naturally right justify.

=LEN(INDEX(tblAccounts,2,1))
8, As expected. Previous tests in a helper column =LEN(A3) return same result

=C2=INDEX(tblAccounts,2,1))
I altered slightly. The value is in Col A and 3rd row so I altered to this:
=A3=INDEX(tblAccounts,3,1). Yields TRUE as expected.

Thanks again, Aladin.
w

And:

=VLOOKUP(A2,tblAccounts,2,0)

where A2 = 60110010, yields #N/A?

 

[Universo Excel]

Have you tried using the =EXACT() function to verify that both numbers are one and the same?

 

[goss]

Thanks Snoopyhr,

I'm not sure I follow where you are trying to lead.
I did go to a clean book and typed '60110020.
It naturally left justified and added a green triangle to upper left corner of the cell.

I then typed 60110020 in another cell (no leading apostrophe).
This value naturally right justified and there is no green triangle in the upper left corner of the cell.

I then ran David McRitchie's TrimAll macro http://dmcritchie.mvps.org/excel/join.htm#trimall on the first cell.
This caused the leading apostrophe to be removed, the contents of the cell to naturally right justify and the green triangle to disappear.

The original data was pulled from an Essbase Cube. It stars out with many leading spaces depending on hierarchy level.

 

[goss]

Interesting.

I tried a vlookup() on the same sheet as the ref array.
It worked.

So I copied the criteria from the ref array and pasted over the criteria on the lookup sheet
Now the lookup worked correctly for that test.

The very next cell down contains the same value 60110020
So I tried the EXACT() function as recommnded by Universo.
=EXACT(C458,C459) = TRUE

So if they are exactly the same, why does one VLOOKUP work and the other does not?

thx
w

 

[Universo Excel]

Interesting.

I tried a vlookup() on the same sheet as the ref array.
It worked.

So I copied the criteria from the ref array and pasted over the criteria on the lookup sheet
Now the lookup worked correctly for that test.

The very next cell down contains the same value 60110020
So I tried the EXACT() function as recommnded by Universo.
=EXACT(C458,C459) = TRUE

So if they are exactly the same, why does one VLOOKUP work and the other does not?

thx
w

Have you checked that the value you are looking for on the resulting Essbase worksheet also passes the EXACT() test?