Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel VLookup Problem

Posted on 2011-03-05
13
Medium Priority
?
589 Views
Last Modified: 2012-05-11
I am preparing a cost estimate in Excel 2010, and have also created a table with a set of cost rates for each item.
In the rates column in the cost estimate, I have used a vlookup function to insert the rate for the corresponding item.  Its all working OK except for 2 items 'DLH' and 'LDE'.  I get #N/A for those items.  Have checked using same value category (eg text), and typing exactly same text. But still not resolved.  If I change item description to say 'xxx', the problem goes and the corresponding rate appears.  Seems to not like anything that starts with 'D'.
Hope someone can help
0
Comment
Question by:gh_user
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044602
Can I see the file? Remember that Vlookup needs exact text. If the lookup value has even a small space, it will give you an error.

Sid
0
 

Author Comment

by:gh_user
ID: 35044691
Thanks
Here is file.
Abbreviation LD causing problem as can be seen.
For data logger, wanted to use DL, but same problem.  So have shown as x1 at the moment. Price-Est.xls
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35044698
Change the formula to

=VLOOKUP(C43,Rates,5,0)

and now try it.

Sid
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044700
Updated it for the entire column. Sample File Attached.

Sid
Price-Est.xls
0
 

Author Comment

by:gh_user
ID: 35044753
Thanks..that works...what did adding the '0' do to the formula?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044768
The syntax of Vlookup is

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Where Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. Here is an extract from Excel's Help.

Range_lookup  A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match:

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.
The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. For more information, see Sort data.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

HTH

Sid



0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35044775
I forgot to mention that you may either use 0 or FALSE which is basically the same.

Sid
0
 
LVL 5

Expert Comment

by:wellous
ID: 35044786
Thanks Sid,and gh
Here is the file , fully updated :)
Brgds,
Wellous
copyPrice-Est.xls
0
 

Author Closing Comment

by:gh_user
ID: 35045195
Very quick response also.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35046843
If you're using 2010, you may as well make use of it's functions...

=IFERROR(VLOOKUP(C8,Rates,5,0),"Error")

This will take care of anything where there is no value found in your lookup table (i.e. Rates).  If you're worried about backwards compatibility, you'll need a slightly longer, slower formula...

=IF(ISNA(VLOOKUP(C8,Rates,5,0)),"Error",VLOOKUP(C8,Rates,5,0))

HTH
0
 

Author Comment

by:gh_user
ID: 35048297
Thanks HTH
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 35048310
Sorry, HTH means Hope This Helps.  My name is Zack (I should have put that in there!).  :)
0
 

Author Comment

by:gh_user
ID: 35048348
Doppy me...thanks firefytr
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question