Solved Excel VLOOKup not returning the correct value.

Microsoft Office 2010 home and business
June 24, 2012 at 21:06:12
Specs: Windows 7, 4 gig
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.

See More: Excel VLOOKup not returning the correct value.

Report •


✔ Best Answer
June 25, 2012 at 08:06:53
2 things come to mind:

1 - Why are you using a range_lookup argument 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_lookup argument to FALSE (or 0) and see what happens.

2 - If changing the range_lookup argument 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 your table_array.

An incorrect return value when using a range_lookup argument of TRUE (or 1) coupled with a #N/A return value when using a range_lookup argument of FALSE (or 0) would lead me towards thinking that there is a subtle difference between your lookup_value and the data in your table_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.



#1
June 25, 2012 at 06:50:57
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.


Report •

#2
June 25, 2012 at 07:29:34
	
	
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


Report •

#3
June 25, 2012 at 07:40:44
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))


Report •

Related Solutions

#4
June 25, 2012 at 08:06:53
✔ Best Answer
2 things come to mind:

1 - Why are you using a range_lookup argument 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_lookup argument to FALSE (or 0) and see what happens.

2 - If changing the range_lookup argument 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 your table_array.

An incorrect return value when using a range_lookup argument of TRUE (or 1) coupled with a #N/A return value when using a range_lookup argument of FALSE (or 0) would lead me towards thinking that there is a subtle difference between your lookup_value and the data in your table_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.


Report •

#5
June 25, 2012 at 09:07:18
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


Report •

#6
June 25, 2012 at 11:30:20
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.


Report •


Ask Question