VLOOKUP Fails Sometimes

goss

Active Member
Joined
Feb 2, 2004
Messages
372
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?
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
how about the length of GL codes, is it possible when they are imported some have hidden spaces ? in you lookup formula are you using trim function ? just a suggestion, is hard to guess why the the lookup fails, in general it fails because the lookup value and lookup array are not same ( 12-1234 not the same as 12-1234 , I added four spaces after 4) hope this help, and if you post a sample of you sheet it helps to trouble shoot it better.
 
Upvote 0
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?

Does something like...

=IFERROR(VLOOKUP(E2&"",$A$2:$B$4,2,0),IFERROR(VLOOKUP(E2+0,$A$2:$B$4,2,0),""))

succeed, where E2 houses a look up value and A2:B4 stands for a table of interest?
 
Upvote 0
Thanks Jythier and Aladin,

@Jythier, All are numbers in bothe the reference array and as the criteria, everything is right justified naturally.

@Aladin, I updated your formula slightly with the named range:

tblAccounts
='i2'!$A$1:$B$2250

=IFERROR(VLOOKUP(C2&"",tblAccounts,2,0),IFERROR(VLOOKUP(C2+0,tblAccounts,2,0),""))

Your formula produces the same correct results as the previous VLOOKUP. Now where I was seeing an error such as #N/A, I see a blank cell.

So the problem persists as the value under inspection is definitely a number on both sheets and definitely exists on both sheets. In fact two of the items that are failing appear in the ref array on rows 2 and 9 respectively.

Thanks
w
 
Upvote 0
Thanks Jythier and Aladin,

@Jythier, All are numbers in bothe the reference array and as the criteria, everything is right justified naturally.

@Aladin, I updated your formula slightly with the named range:

tblAccounts
='i2'!$A$1:$B$2250

=IFERROR(VLOOKUP(C2&"",tblAccounts,2,0),IFERROR(VLOOKUP(C2+0,tblAccounts,2,0),""))

Your formula produces the same correct results as the previous VLOOKUP. Now where I was seeing an error such as #N/A, I see a blank cell.

So the problem persists as the value under inspection is definitely a number on both sheets and definitely exists on both sheets. In fact two of the items that are failing appear in the ref array on rows 2 and 9 respectively.

Thanks
w

Would you post that look up value and its counterpart from the table?
 
Upvote 0
Hi Aladin,

Sorry for the delay.

Any thoughts on how I could post enough relevant information to give a sample of the problem while protecting company proprietary information?

Thanks
w
 
Upvote 0
Hi Aladin,

Sorry for the delay.

Any thoughts on how I could post enough relevant information to give a sample of the problem while protecting company proprietary information?

Thanks
w

I was asking for just one look up value and the value in the table the formula fails to match...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top