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 10/12/2012, supposed :
=LOOKUP(C1,{1,20,50,75},{"Low","Med","High","Heavy "})

Howard,
Exactly what I would do in a real project because it's more efficient
(and self-explanatory to me). I chose to follow the OP's logic
combining IF/AND functions for learning benefit (hopefully).<g

--
Garry

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

Actually, my above statement is not entirely true. I like a clean
worksheet and so try to avoid looking at errors returned by formulas.
My first offering accomplishes this if the lookup value is outside the
criteria range[s]. The way I'd use LOOKUP normally would to wrap it in
an IF function so it returns an empty string on error...


=IF(ISERROR(LOOKUP($A1,{1,20,50,75},{"low","med"," high","heavy"})),"",LOOKUP($A1,{1,20,50,75},{"low" ,"med","high","heavy"}))

...and so we lose formula brevity. I this OP's scenario, outside the
range is any value <1. IMO, it's better to *not* have errors propagate
to other cells containing formulas that ref cells with formulas that
return errors!

--
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 Monday, December 10, 2012 12:06:45 PM UTC-8, GS wrote:
on 10/12/2012, supposed :

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



Howard,

Exactly what I would do in a real project because it's more efficient

(and self-explanatory to me). I chose to follow the OP's logic

combining IF/AND functions for learning benefit (hopefully).<g



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Hi Gary,

"...combining IF/AND functions for learning benefit (hopefully).<g "

When it comes to that many IF/AND's (and let's not forget OR) I struggle. I should take your gentle advise and become more adept with them, but it is way to easy go the LOOKUP route.

Howard

brought next idea :
On Monday, December 10, 2012 12:06:45 PM UTC-8, GS wrote:
on 10/12/2012, supposed :

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



Howard,

Exactly what I would do in a real project because it's more efficient

(and self-explanatory to me). I chose to follow the OP's logic

combining IF/AND functions for learning benefit (hopefully).<g



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Hi Gary,

"...combining IF/AND functions for learning benefit (hopefully).<g "

When it comes to that many IF/AND's (and let's not forget OR) I struggle. I
should take your gentle advise and become more adept with them, but it is way
to easy go the LOOKUP route.

Howard

I agree, and as I said to Howard it's how I would do it, just for the
simplicity. Did you note my 2nd version to address errors if the lookup
value cell is empty OR contains a value <1?

--
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") )))