Need help with Formula

[YogaTaco]

Hi everyone!
I'm a total newbie with Excel and need help with a formula.
Trying to work on a rank system, but get Value error or N/A most of the time.
What i need is, when cell B2 is between 1-179 pieces, cell C2 should turn to 3. Cell B3 between 180-259 should turn C3 to 3,1.
It's multiple because the rank system goes from 3-7,7.
Thanks in advance

 

[Joe4]

Welcome to the Board!

Update your table so that only the lower values are shown in column F.
So F2 should be 1, F3 should be 180, and F4 should be 260.

Then, use this formula in C2 and copy to C3:

=VLOOKUP(B3,$F$2:$G$4,2,1)

Note that depending on regional settings, you may need to change all commas to semi-colons in the formula.

Also, if you want it to stop at 339, you will need to add another row to your lookup table that has 340 in column F and what you want to return if it is more than that in column G.

 

[YogaTaco]

Welcome to the Board!

Update your table so that only the lower values are shown in column F.
So F2 should be 1, F3 should be 180, and F4 should be 260.

Then, use this formula in C2 and copy to C3:

=VLOOKUP(B3,$F$2:$G$4,2,1)

Note that depending on regional settings, you may need to change all commas to semi-colons in the formula.

Also, if you want it to stop at 339, you will need to add another row to your lookup table that has 340 in column F and what you want to return if it is more than that in column G.

Thanks for the quick reply!
Will check it tomorrow and will let you know if it worked.
Thanks again

 

[YogaTaco]

Hi Joe4
formula worked! Thank you very much!
Altough, i have a question about the formula.
=VLOOKUP(B3,$F$2:$G$4,2,1).. what 2,1 stand for?

Thanks again

 

[Joe4]

You are welcome.

If you use the Excel function helper, it tells you what each argument of the function is.
Also, you can find complete descriptions and examples of Excel functions on the web with a quick Google search.
Here is a good one: MS Excel: How to use the VLOOKUP Function (WS)

Note that for the last argument, 1 is the numeric equivalent of FALSE, while 0 is the numeric equivalent of TRUE.
Just a little shortcut for those of us too lazy to type out TRUE or FALSE.

 

[Dave Patton]

Check Excel's help for the function

it includes
In its simplest form, the VLOOKUP function says:
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).