Microsoft Office 2010 home and business

VLOOKup table yields a value one row ABOVE the look up value which is alpha-numeric. I've used VLookup many times and never had this happen. I double checked the range, etc but everything seems to be in order.

✔ Best Answer

2 things come to mind: 1 - Why are you using a

range_lookupargument of TRUE?Aren't you looking for exact matches? TRUE (or 1) is when you are looking for an approximate match. Review the Help file for VLOOKUP for an explanation.

Try changing

range_lookupargument to FALSE (or 0) and see what happens.2 - If changing the

range_lookupargument to FALSE (or 0) returns a #N/A, then you don't have an exact match between the value in E110 and any value in yourtable_array.An incorrect return value when using a

range_lookupargument of TRUE (or 1) coupled with a #N/A return value when using arange_lookupargument of FALSE (or 0) would lead me towards thinking that there is a subtle difference between yourlookup_valueand the data in yourtable_array.I would look for leading or trailing spaces or other hidden characters.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

It's really hard for us to help since all you did was tell us what the end result was. There's no example data, there's no example formula.

Since we can't see your spreadsheet from where we're sitting, nor can we read minds, it's basically impossible for us to help you.

If you decide to help us help you by posting some example data, be sure to read the following line before posting.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Row Labels Sum of SHORTAGE-14 W56B4P1320 10400 W56BLT910106 59000 W56EG42510S 145000 W56EG43010L 120000 W56EG45010S 10000 W56EG45020L 280000 W56GG451520L 2920 W56GG455010L 45000 W56GM43010A 396000 W56GM44010A 172000 W56L601610 444822 W56L602010 1396000

I used this formula and for Item Number W56EG43010L (Col A) it returns 145,000 instead of 120,000 and the same pattern for any Item No. =IF(ISBLANK($E110),0,VLOOKUP($E110,'Shortage Totals by Item'!$A$4:$B$87,2,TRUE))

2 things come to mind: 1 - Why are you using a

range_lookupargument of TRUE?Aren't you looking for exact matches? TRUE (or 1) is when you are looking for an approximate match. Review the Help file for VLOOKUP for an explanation.

Try changing

range_lookupargument to FALSE (or 0) and see what happens.2 - If changing the

range_lookupargument to FALSE (or 0) returns a #N/A, then you don't have an exact match between the value in E110 and any value in yourtable_array.An incorrect return value when using a

range_lookupargument of TRUE (or 1) coupled with a #N/A return value when using arange_lookupargument of FALSE (or 0) would lead me towards thinking that there is a subtle difference between yourlookup_valueand the data in yourtable_array.I would look for leading or trailing spaces or other hidden characters.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

DerbyDad03, It works! I had actually tried the False but just got the #N/A. I never combined the two results (TRUE -incorrect and FALSE - #N/A) as a pointer to the root cause. I went back to the Imported Source file (that I built my Vlookup on) and sure enough, some but not all of the Item No. fields had a trailing SPACE. I used =Trim(argument cell) in a new column called Item No. clean in the Source file, then went back to the Pivot Table, unchecked the original Item number field which collapsed the Pivot Table just showing the grand total, then clicked Refresh and the new column was listed; I checked it and presto the correct numbers appeared on my work sheet.

Thanks so much for the help! BillStan

I'm glad you worked it out. The next time you need some help, include the sample data and related formula(s) in your OP so you can get an answer without a bunch of back and forth.

Many people simply ignore posts where there is not enough information to even get started working on an answer.

As moderator, I'm sort of obligated to point out the correct way to post a question.

Let us know if we can be of any further assistance.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History