# Vlookup question

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?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:

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.
Author Commented:
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
Commented:
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.

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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?
Commented:
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.
Author Commented:
I will have to have my lookup data cleaned up.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.