?
Solved

Vlookup question

Posted on 2014-01-06
6
Medium Priority
?
305 Views
Last Modified: 2014-01-06
I am trying to do a vlookup but when I complete the formula I get #ref.  My formula is as follows:
vlookup(i2,Tiers!$a$2:$a$282,3,true)

I know I won't get a result for all of my entries, which is why the next step will be to do an If statement, but I know there should be some entries with a value returned.  Any ideas?
0
Comment
Question by:Rrave26
[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
  • 3
  • 2
6 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39760557
your third paramter= '3'.

That means to use the 3rd column of your range.

But the range you specified only contains 1 column... column "a"..... Tiers!$a$2:$a$282

supposing that Tiers!$a$2:$a$282 contains the item you are trying to match on... change the 3rd parameter from a 3 to a 1.
0
 

Author Comment

by:Rrave26
ID: 39760676
Ok, so I changed my parameters but now I'm returnning incorrect results.  What am I missing here.  I am attaching my ws for your review.
TEST-IM-METRICS-TRACKING.xlsm
0
 
LVL 12

Accepted Solution

by:
Harry Lee earned 1000 total points
ID: 39760735
Rrave26,

What do you mean by incorrect results?

There are 2 reasons why you may get wrong result. In you formula,
=VLOOKUP(I2,Tiers!A2:C282,3,TRUE)

You are stating you want to search for the closes result by having the ,TRUE at the back. To get absolute match, you should use ,False.

When you have it set to ,False, you will end up getting error like #N/A in pretty much all of them because your lookup value and your data table is different. Almost all of your lookup value has a space behind them making absolute matching fail.

To get the lookup accurate, you have to delete all the trailing space in column I on your IM Raw Data sheet. 2nd, change your formula from =VLOOKUP(I2,Tiers!A2:C282,3,TRUE) to =VLOOKUP(I2,Tiers!A2:C282,3,FALSE).

After than, you will still have a few #N/A because the lookup value is not in the Tiers sheet.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:Rrave26
ID: 39760776
What I mean by incorrect for example is on line 1 and 23 it returns a value of 1 when there isn't a match.  I have deleted all of the trailing spaces and there wasn't any changes.

What else am I missing?
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39760808
Rrave26, that's what I mean by closes match by having the True at the end of your formula.

The True means Approximate Match, and False means Exact Match.

What happen is the vlookup is not able to find a match, and it will pick the 1 cell after the closes match.

Let say, you are looking for Home in your vlookup, and in the data table, you only have Homa, and Hello. It will retuen 20, which is the data of Hello, instead of telling you there is no match.

          A               B
1     Homa          10
2     Hello          20
3     Helper          30
4     Howe          40

If you use False at the end, in the above sample, it will return #n/a since there is no Home in your data list.
0
 

Author Closing Comment

by:Rrave26
ID: 39760829
I will have to have my lookup data cleaned up.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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