Excel vLookup not working
I'm stumped as to why C24 (and a few others in that set) does not work. it does seem to work if i use numbers instead of text.
I'm sure this should work with text.
Any help?
vlookup-test.xlsx
I'm sure this should work with text.
Any help?
vlookup-test.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
P.S. in what way is 'Melon' similar to 'Pear' how does Microsoft decide this!!!
Microsoft had to match what Lotus 123 did with their VLOOKUP. So the syntax is admittedly confusing.
TRUE means that the first column of the lookup table has been sorted in ascending order, and you do not require an exact match. VLOOKUP uses a binary search, so it is very fast. It is good for bracket pricing (or income tax calculations), where the first column of the lookup table lists the bottom of each bracket. Suppose the first two bracket bottoms are 0 and 5. If A24 equals 1, then VLOOKUP will return a value from the 0 row of the lookup table.
FALSE means that the first column of the lookup table has not been sorted. An exact match is required, so VLOOKUP searches each row of the lookup table (much slower than a binary search). For price lists, employment records and the like, this is the form of VLOOKUP that you would need.
While your remark about the similarity of Melon and Pear is right on point, VLOOKUP with TRUE would be used more appropriately with a lookup table like this:
Qty Disc. Bracket
0 0% 0 to 4
5 10% 5 to 9
10 15% 10 to 24
25 20% 25 to 99
100 25% 100 and over
TRUE means that the first column of the lookup table has been sorted in ascending order, and you do not require an exact match. VLOOKUP uses a binary search, so it is very fast. It is good for bracket pricing (or income tax calculations), where the first column of the lookup table lists the bottom of each bracket. Suppose the first two bracket bottoms are 0 and 5. If A24 equals 1, then VLOOKUP will return a value from the 0 row of the lookup table.
FALSE means that the first column of the lookup table has not been sorted. An exact match is required, so VLOOKUP searches each row of the lookup table (much slower than a binary search). For price lists, employment records and the like, this is the form of VLOOKUP that you would need.
While your remark about the similarity of Melon and Pear is right on point, VLOOKUP with TRUE would be used more appropriately with a lookup table like this:
Qty Disc. Bracket
0 0% 0 to 4
5 10% 5 to 9
10 15% 10 to 24
25 20% 25 to 99
100 25% 100 and over
ASKER
However i had misunderstood the last option, i thought the true was setting it to find exact matches only, but it appears that false does that.
This has been driving me nuts for an hour.......oh how i love and hate formulas!!!