Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

Excel VLookup Problem

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
gh_user
Asked:
gh_user
  • 5
  • 5
  • 2
  • +1
1 Solution
 
SiddharthRoutCommented:
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
 
gh_userAuthor Commented:
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
 
SiddharthRoutCommented:
Change the formula to

=VLOOKUP(C43,Rates,5,0)

and now try it.

Sid
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
SiddharthRoutCommented:
Updated it for the entire column. Sample File Attached.

Sid
Price-Est.xls
0
 
gh_userAuthor Commented:
Thanks..that works...what did adding the '0' do to the formula?
0
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
I forgot to mention that you may either use 0 or FALSE which is basically the same.

Sid
0
 
wellousCommented:
Thanks Sid,and gh
Here is the file , fully updated :)
Brgds,
Wellous
copyPrice-Est.xls
0
 
gh_userAuthor Commented:
Very quick response also.
0
 
Zack BarresseCEOCommented:
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
 
gh_userAuthor Commented:
Thanks HTH
0
 
Zack BarresseCEOCommented:
Sorry, HTH means Hope This Helps.  My name is Zack (I should have put that in there!).  :)
0
 
gh_userAuthor Commented:
Doppy me...thanks firefytr
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now