vlookup Help

[Flemminr]

I'm trying to use the vlookup function for an assignment where I need to figure out the total price based on the number of ads a person wants to buy. The more ads, the cheaper it is, and they enter the number they want into a cell. I have it all figured out but my problem is that I need to have the number of ads column in my table set up by the following ranges: 1-5, 6-10, 11-20, 21+. How do I get the vlookup function to reference those ranges from a single cell instead of just one number in a single cell? Any suggestions are appreciated, thanks!

[AranDG]

I'm assuming your ranges have a set price across the range? So 1-5 are prcied the same, 6-10 are priced the same etc etc.
If this is the case, then you don't need to reference a range, just the lower limit of the range.

Ok, so your lookup table would look something like this (below), with your lower range limits listed and their respective prices listed next to them.

Range,Value
1,1
6,0.75
11,0.5
21,0.25

Then you're just using a VLOOKUP as follows (assuming the lookup table spans columns A & B and the value you're looking up resides in cell C1)

=VLOOKUP(C1,A1:B4,2)

Now it doesn't matter where your value resides in the range as it will always take the closest value less than the value you are looking up. So if the value is 5, the lookup will return the price for range 1 and so on

[Flemminr]

I'm assuming your ranges have a set price across the range? So 1-5 are prcied the same, 6-10 are priced the same etc etc.
If this is the case, then you don't need to reference a range, just the lower limit of the range.

Ok, so your lookup table would look something like this (below), with your lower range limits listed and their respective prices listed next to them.

Range,Value
1,1
6,0.75
11,0.5
21,0.25

Then you're just using a VLOOKUP as follows (assuming the lookup table spans columns A & B and the value you're looking up resides in cell C1)

=VLOOKUP(C1,A1:B4,2)

Now it doesn't matter where your value resides in the range as it will always take the closest value less than the value you are looking up. So if the value is 5, the lookup will return the price for range 1 and so on

My problem is that I need the table to display 1-5, 6-10, 11-20, 21+ (assignment requirements) but when vlookup goes to calculate it doesn't work because it is a range and not a single number. If I change it to a single number, then my vlookup function works but with the number range I just get #N/A

[FlameRetired]

Withdrawn by FR.

[Flemminr]

Doesn't look like it.

Got some of the data in an Excel workbook to upload?

Looks like it just got moved. The workbook is attached. I can't have any additional columns so I need it to work using just the one column I already have.

[AranDG]

Is using VLOOKUP a requirement?

[Flemminr]

Yeah, I have to use vlookup.

[skywriter]

Try this. I tested it, it works fine. You want to change the number of ads and get the right price per ad and the total cost of those ads using that correct price?
This will do that. Put this formula in cell C16.

=LOOKUP(D14,(LEFT(C7:C10,2)*1),D7:D10)*D14

[skywriter]

Yeah, I have to use vlookup.

I saw your first post about this being an assignment. Is this a school assignment?
Maybe there is some very tricky way to do this with VLOOKUP, but I don't see it.
First thing I should point out to you is that the values you have under your
Number of Ads headers are not numbers
1-5
6-10
these are not numbers each of these is a string. So you can't VLOOKUP the number 6 for six ads from that. Try vlookup and tell vlookup you want to find "1 - 5" and you will get a value. My solution above post #8 changes the left string numbers to actual numbers and that's why it works.
A formula that would work, but it doesn't actually use your table is the following:

=VLOOKUP(D14,{1,10000;6,9000;11,8000;21,7000},2,1)*D14

[Flemminr]

Yeah, it's for a uni class on Excel. All the examples we covered were ones that had separate columns for the lower and upper values, so that was why I turned here for help. I've attached the instruction sheet if that helps. He said explicitly in class the other day that it had to look exactly like the pictures in the instructions.

[skywriter]

Yeah, it's for a uni class on Excel. All the examples we covered were ones that had separate columns for the lower and upper values, so that was why I turned here for help. I've attached the instruction sheet if that helps. He said explicitly in class the other day that it had to look exactly like the pictures in the instructions.

Okay here's your solution.
Go into cell C7 and change what is there to the number 1, trust me, just do it, I'll explain below.
Go into C8 type 6, C9 type 11, C10 type 21.
Now right click on cell C7, choose format cells then go into the number tab and change the selection to custom, in the box under the word "Type:" put the following 0 " - 5", then click okay, now your cell looks like this 1 - 5, but the true value in the cell is just the number 1, so you can now use vlookup. Your instructor just says the users sees this, and this will look exactly like that.
For the next boxes you should be able to figure it out from my example. The 0 is a place holder for the number and " " anything between the quotes will look just like what you type, so for the 21 you would use "+" and it will look like you want it to.

Good Luck!!!
You must come back and let me know what happens. If your instructor has some other way to do this without doing what I told you I would be absolutely interested in know how. If he uses LOOKUP instead of VLOOKUP then you can say why didn't you use VLOOKUP and if he says it can't be done, show him your solution.

[Flemminr]

Okay here's your solution.
Go into cell C7 and change what is there to the number 1, trust me, just do it, I'll explain below.
Go into C8 type 6, C9 type 11, C10 type 21.
Now right click on cell C7, choose format cells then to into the number tab and change the selection to custom, in the box under the word "Type:" put the following 0 " - 5", then click okay, now your cell looks like this 1 - 5, but the true value in the cell is just the number 1, so you can now use vlookup. Your instructor just says the users sees this, and this will look exactly like that.
For the next boxes you should be able to figure it out from my example. The 0 is a place holder for the number and " " anything between the quotes will look just like what you type, so for the 21 you would use "+" and it will look like you want it to.

Good Luck!!!
You must come back and let me know what happens. If your instructor has some other way to do this without doing what I told you I would be absolutely interested in know how. If he uses LOOKUP instead of VLOOKUP then you can say why didn't you use VLOOKUP and if he says it can't be done, show him your solution.

It worked! Thank you so much!

Although now I have another slight problem. Instead of giving me the total of X amount of ads, it just gives me the price per ad based on the amount I want. I.E. 8 ads is giving me $9,000 instead of $72,000.

[skywriter]

Open the spreadsheet I sent. You have to multiply the formula by the quantity cell.
Number of ads * price.

[AranDG]

Hey, got it, replace C2 with your lookup value:

=VLOOKUP(FLOOR(C2-1,5)+1&IF((INT((C2-1)/5)+1)*5>20," or more","-"&(INT((C2-1)/5)+1)*5),C7:D10,2)

[AranDG]

Hey, got it, replace C2 with your lookup value:

=VLOOKUP(FLOOR(C2-1,5)+1&IF((INT((C2-1)/5)+1)*5>20," or more","-"&(INT((C2-1)/5)+1)*5),C7:D10,2)

Just out of curiosity, would the formula not be a better method than reformatting the sheet?

[Flemminr]

As yes, knew it was something simple. Trying to figure out the vlookup had me trying to overthink it. Thank you so much everyone for your help!

[Flemminr]

Well, the assignments are based on what we've done in class and we've done cell formatting and haven't done the FLOOR function so that was why I went with that.