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?
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

snoopyhr

Active Member
Joined
Aug 12, 2002
Messages
395
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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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?
 

goss

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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?
 

goss

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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...
 

goss

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

Watch MrExcel Video

Forum statistics

Threads
1,123,488
Messages
5,601,974
Members
414,487
Latest member
inxlsplot

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
Top