I am working on an inventory spreadsheet. The formula I need help with is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" between 20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" = 75 then "HEAVY".

I hope that makes sense. Thanks.

Would the VLOOKUP values always be whole numbers or could they be decimals too?

If always whole numbers then a simple VLOOKUP table would work.

If decimals then a long winded IF AND nested formula would be needed.

Spencer, thanks for the reply. The value would always be a whole number. I'm not exactly sure how to use the VLOOKUP table in the formula above though.

I'm replying from an ipad with no access to Excel so this is off the top of my head and possibly a little vague....

You nest your current VLOOKUP inside another one. So where a VLOOKUP starts =vlookup(a1,range... Etc, you replace the A1 with your current VLOOKUP.

Does that make sense?

You can either use ,false) at the end meaning you'd need a lookup table with each possible value or a ,true) at the end meaning you'd only have to have one entry for each low, med high etc.

If you can post an example workbook here or email it to me at pubnut @ gmail . com I will show you what I mean as soon as I get home to my pc.

That may be a little beyond my excel ability, but I'll play around with it. I'll aslo send you an email with an example workbook. Thanks!

Not a problem. I'll be in front of a pc in about half an hour.

On Monday, December 10, 2012 8:03:37 AM UTC-8, CMoHorn wrote:
I am working on an inventory spreadsheet. The formula I need help with

is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" between

20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" =

75 then "HEAVY".



I hope that makes sense. Thanks.









--

CMoHorn

Hi CMoHorn,

See if this works for you.
Where your lookup value is in C1.

=LOOKUP(C1,{1,20,50,75},{"Low","Med","High","Heavy "})

HTH
Regards,
Howard

CMoHorn has brought this to us :
I am working on an inventory spreadsheet. The formula I need help with
is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" between
20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" =
75 then "HEAVY".

I hope that makes sense. Thanks.

Assuming "VLOOKUP VALUE" is the result of a formula in A1, try...


=IF(AND($A10,$A1<20),"LOW",IF(AND($A119,$A1<50), "MEDIUM",IF(AND($A149,$A1<75),"HIGH",IF($A174,"H EAVY",""))))

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Mon, 10 Dec 2012 16:03:37 +0000, CMoHorn wrote:


I am working on an inventory spreadsheet. The formula I need help with
is IF("VLOOKUP VALUE") is between 1-19 then "LOW"; IF "VALUE" between
20-49 then "MEDIUM"; IF "VALUE" between 50-74 then "HIGH"; IF "VALUE" =
75 then "HEAVY".

I hope that makes sense. Thanks.

And here is another that assumes VALUE will always be an integer, and that you have not defined what you want returned if VALUE is not a number =1:

=IF(OR(A1<1,NOT(ISNUMBER(A1))),"undefined",IF(A1<2 0,"LOW",IF(A1<50,"MEDIUM",IF(A1<75,"HIGH","HEAVY") )))