Rrave26
asked on
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$2
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?
vlookup(i2,Tiers!$a$2:$a$2
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?
ASKER
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
TEST-IM-METRICS-TRACKING.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
What else am I missing?
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.
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.
ASKER
I will have to have my lookup data cleaned up.
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.