How Do I Program Multiple Conditions within a Cell in Excel?

[Ocean Zhang]

Trying to program cell C1:

If A1 is between .81 and 3, AND if B1 is Adult, then C1 is Priority Mail
If A1 is greater than 3, AND if B1 is Adult, then C1 is FedEx Ground
If A1 is Less than .81, AND if B1 is Adult, then C1 is First Class Mail
If A1 is Less than .81, AND if B1 is Child, then C1 is Hold for Inspection

etc.

There will be a list of ~45 conditions that will populate a specific value in C1. Can this be done? If so, how?

Thanks
Ocean

[NBVC]

Create separate lists for Adult, Child, etc...

show the bottom ranges in ascending order starting from 0 in one column and next to it the text string you would like returned... then use Lookup() function with IF() to get data..

e.g. =IF(B1="ADULT",Lookup(A1,Adult_Range),if(B1="CHILD",Lookup(A1,Child_Range), etc..)

or shorter

=Lookup(A1,IF(B1="ADULT",Adult_Range,if(B1="CHILD",Child_Range),etc...)

or if you further name each range like ADULT, CHILD...

Then simply: =LOOKUP(A1,INDIRECT(B1))

[Ocean Zhang]

Thanks for your help, but I'm having a hard time understanding. I must have read over your post over a hundred times and I still can't get it

Can you perhaps attach a short example?

Thanks
Ocean

[NBVC]

Ok, see attached....

I showed 2 options...

First Option is in the case that you have different ranges for the different categories (i.e. Adult, Child, etc.)

So I listed the Adult specs in I1:J4 and the Child specs in K1:L4

I named each of those 2 tables through Insert|Name|Define as "Adult" and "Child" respectively...

Then formula in C1 is:

=LOOKUP(A1,IF(B1="ADULT",Adult,IF(B1="CHILD",Child,"")))

This looks at value in A1 in one of those 2 tables based on what is in B1.

Option 2 is for if the ranges are always the same but you have different text returned based on the category..

So now I named range I8:J11 as "AdultRange" and then I8:K11 as "ChildRange".. Note: Lookup() looks up in the first column of the range and returns from the last column in the specified range...

Formula in C9:

=LOOKUP(A9,IF(B9="ADULT",AdultRange,IF(B9="CHILD",ChildRange,"")))

Test out which ever you prefer and apply...

[NBVC]

Here is a sample using the shorter: =Lookup(A1,Indirect(B1)) if you name the ranges as per what you write in B1...

See formula in D1

[Ocean Zhang]

Thank you SOOOOOOOO much for your help. This will be extremely helpful. I couldn't get your second example to work though. I don't think I'm naming the ranges or tables correctly.

I have another related question. Should I start a new thread or post the question within this one?

Thanks!
Ocean

[NBVC]

Probably should be a new thread.. unless it is directly related to the same initial question here.