Why doesn't my vlookup formula work?

Factual Questions
1

I am trying to create a vlookup formula in Excel, and it only sorta works. I’ve created a table that calculates readership of a newspaper ad based on the size of the ad. Then I created two pull-down lists using the different ad sizes as the source data. What I want to do is to be able to choose “if you go from this ad size x to this ad size y you lose this many readers z.”

So, I was wanting to create two vlookup formulas, the first to return the beginning readership, and the second returning the smaller ad readership so I could then create a third formula to subtract the second from the first and return the number of readers lost.

So, first I created the table, which looks like this:

FulL 2,700
Three 2,100
Half 1,500
Quarter 1,500
Eighth 1,350

(The readership numbers themselves are a result of a formula.)

Then I created the two cells with the pull-down lists.

BUT when I create the vlookup formula, it correctly returns the readership number of the pull-down option is set to Full, Half, or Quarter. It returns the Eighth number if set to Three and returns !NA# if set to Eighth.

???

Here’s what I’ve done to try to fix this:

[ul]
[li]Set the ad-size fields to general. Didn’t help.[/li][li]Set the ad-size fields to text. Didn’t help.[/li][li]Used Three Quarter instead of Three. Didn’t help.[/li][li]Used text of 3/4, 1/2, etc. Same result.[/li][li]Used the symbols for 3/4, 1/2, etc. Same result.[/li][li]Made sure there were no spaces before any of the words. Didn’t help.[/li][li]Made sure all of the ad-size fields were set to left justified. Well, you can guess the result.[/li][li]Deleted my spreadsheet. Went home and slept. Came back in the morning and created another from scratch. Same results. [/li][/ul]

No matter what I do, I get the correct result for Full, Half, and Quarter. The Eighth number for Three and an error for Eighth.

The pull down list is D18, the table is C12:D16, so the formula looks like this:

=VLOOKUP($D$18,C12:D16,2)

The hell???

(Also, if you could tell me how to keep the title of my thread from showing up in my OP, I’d love you for ever and ever.)

2

Of course the smilies are supposed to be colons. Damn my CEO for calling right when I’m trying to edit!!!

Cuz, if I meant to put smilies in my formula, considering how befuzzled I am that I’m getting such weird results, it woulda been this one ->:mad:

3

I think I understand what you’re trying to do…but it worked for me when I replicated it. This is the only change I made:=VLOOKUP(D18,c12 : c16,2,0)

(in other words, after the 2, I added “, 0”. I’m not sure why, but that’s the way I learned to write a vlookup, and it worked when I tried it.

4

(also, in the post above, I added spaces before and after the colon 'cause I don’t know how to make it not show up as a smiley)

5

The last parameter tells the formula whether you are using an ordered list or not. It makes the lookup faster if it’s pre-sorted and it can stop searching when the lookup value is past the highest value in sort order.

So, since your list is not in sort order, you can either sort it or just add “FALSE” (or 0) as the last argument to the formula.

6

Excellent. I’ve learned something new today. It’s never mattered in the past, so I never used that last parameter. It will now be my default. Thanks batsto and gumpy.