[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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
0
tetrauk
Asked:
tetrauk
  • 2
  • 2
1 Solution
 
byundtCommented:
For C24, you should be using a formula like:
=VLOOKUP(A24,PricingTable,IF($B$24>10,3,IF($B$24>5,4,5)),FALSE)

Your IF function had been referencing the wrong row (row 23). Also, the VLOOKUP should use FALSE as the last parameter because approximate matches for A24 are not acceptable--you want an exact match.
0
 
tetraukAuthor Commented:
The locked reference for B23 was fine, that was deliberate, the sheet is not actually counting fruit in real life.

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!!!
0
 
tetraukAuthor Commented:
P.S. in what way is 'Melon' similar to 'Pear' how does Microsoft decide this!!!
0
 
byundtCommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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