is there an alternative to using IF, as I believe one is limited to 7 IF
statements in a formulae.

My scenario is;

Column A contains 15 different values, let's say
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29
Column B has corresponding Letters A,B,C,D,E,F,G,H,I,J,K,L,M,N,O

I would like to run a formula against approx 400 numbers, (most between 0 &
29) and return the corresponding letter

ie
IF A1 <=1,A,IF A1 <=3,B etc etc - obviously I can't use IF because I am
limited to 7 IFs.

Any suggestions greatly appreciated !!

On Fri, 28 Mar 2008 08:40:03 -0700, Dave P <Dave
wrote:

is there an alternative to using IF, as I believe one is limited to 7 IF
statements in a formulae.

My scenario is;

Column A contains 15 different values, let's say
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29
Column B has corresponding Letters A,B,C,D,E,F,G,H,I,J,K,L,M,N,O

I would like to run a formula against approx 400 numbers, (most between 0 &
29) and return the corresponding letter

ie
IF A1 <=1,A,IF A1 <=3,B etc etc - obviously I can't use IF because I am
limited to 7 IFs.

Any suggestions greatly appreciated !!

Use VLOOKUP

--ron

I can't use VLOOKUP because the 400 or so values that I have do not match the
values in my column A, they are for example 1.3, 5.6 or 27.1

"Ron Rosenfeld" wrote:

On Fri, 28 Mar 2008 08:40:03 -0700, Dave P <Dave
wrote:

is there an alternative to using IF, as I believe one is limited to 7 IF
statements in a formulae.

My scenario is;

Column A contains 15 different values, let's say
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29
Column B has corresponding Letters A,B,C,D,E,F,G,H,I,J,K,L,M,N,O

I would like to run a formula against approx 400 numbers, (most between 0 &
29) and return the corresponding letter

ie
IF A1 <=1,A,IF A1 <=3,B etc etc - obviously I can't use IF because I am
limited to 7 IFs.

Any suggestions greatly appreciated !!

Use VLOOKUP

--ron

On Fri, 28 Mar 2008 09:13:00 -0700, Dave P
wrote:

I can't use VLOOKUP because the 400 or so values that I have do not match the
values in my column A, they are for example 1.3, 5.6 or 27.1

That is irrelevant.

Read HELP for VLOOKUP and pay particular attention to the optional range_lookup
argument.

--------------------------
range_lookup Optional. A logical value that specifies whether you want VLOOKUP
to find an exact match or an approximate match:

If range_lookup is either TRUE or is omitted, an exact or approximate match is
returned. If an exact match is not found, the next largest value that is less
than lookup_value is returned.
-----------------------------------
--ron

An alternative is to calculate it in D1:

=CHAR(65+INT(C1/2))

assuming your numbers are in column C - copy down to cover your 400
numbers.

Hope ths helps.

Pete

On Mar 28, 4:13*pm, Dave P wrote:
I can't use VLOOKUP because the 400 or so values that I have do not match the
values in my column A, they are for example 1.3, 5.6 or 27.1



"Ron Rosenfeld" wrote:
On Fri, 28 Mar 2008 08:40:03 -0700, Dave P <Dave
wrote:

is there an alternative to using IF, as I believe one is limited to 7 IF
statements in a formulae.

My scenario is;

Column A contains 15 different values, let's say
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29
Column B has corresponding Letters A,B,C,D,E,F,G,H,I,J,K,L,M,N,O

I would like to run a formula against approx 400 numbers, (most between 0 &
29) and return the corresponding letter

ie
IF A1 <=1,A,IF A1 <=3,B etc etc - obviously I can't use IF because I am
limited to 7 IFs.

Any suggestions greatly appreciated !!

Use VLOOKUP

--ron- Hide quoted text -

- Show quoted text -

thanks - I will give both solutions a try

"Pete_UK" wrote:

An alternative is to calculate it in D1:

=CHAR(65+INT(C1/2))

assuming your numbers are in column C - copy down to cover your 400
numbers.

Hope ths helps.

Pete

On Mar 28, 4:13 pm, Dave P wrote:
I can't use VLOOKUP because the 400 or so values that I have do not match the
values in my column A, they are for example 1.3, 5.6 or 27.1



"Ron Rosenfeld" wrote:
On Fri, 28 Mar 2008 08:40:03 -0700, Dave P <Dave
wrote:

is there an alternative to using IF, as I believe one is limited to 7 IF
statements in a formulae.

My scenario is;

Column A contains 15 different values, let's say
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29
Column B has corresponding Letters A,B,C,D,E,F,G,H,I,J,K,L,M,N,O

I would like to run a formula against approx 400 numbers, (most between 0 &
29) and return the corresponding letter

ie
IF A1 <=1,A,IF A1 <=3,B etc etc - obviously I can't use IF because I am
limited to 7 IFs.

Any suggestions greatly appreciated !!

Use VLOOKUP

--ron- Hide quoted text -

- Show quoted text -

Mine didn't quite do what you wanted - use this instead:

=CHAR(65+INT((C1+1)/2))

Will give A for less than 1, then B for 1 up to less than 3 etc.

Hope this helps.

Pete

On Mar 28, 4:55*pm, Dave P wrote:
thanks - I will give both solutions a try



"Pete_UK" wrote:
An alternative is to calculate it in D1:

=CHAR(65+INT(C1/2))

assuming your numbers are in column C - copy down to cover your 400
numbers.

Hope ths helps.

Pete

On Mar 28, 4:13 pm, Dave P wrote:
I can't use VLOOKUP because the 400 or so values that I have do not match the
values in my column A, they are for example 1.3, 5.6 or 27.1

"Ron Rosenfeld" wrote:
On Fri, 28 Mar 2008 08:40:03 -0700, Dave P <Dave
wrote:

is there an alternative to using IF, as I believe one is limited to 7 IF
statements in a formulae.

My scenario is;

Column A contains 15 different values, let's say
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29
Column B has corresponding Letters A,B,C,D,E,F,G,H,I,J,K,L,M,N,O

I would like to run a formula against approx 400 numbers, (most between 0 &
29) and return the corresponding letter

ie
IF A1 <=1,A,IF A1 <=3,B etc etc - obviously I can't use IF because I am
limited to 7 IFs.

Any suggestions greatly appreciated !!

Use VLOOKUP

--ron- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You can use VLOOKUP without having exact matching values simply by setting
the last argument of the formula to TRUE instead of FALSE. Problem with
VLOOKUP though is that it looks for the value that's less than or equal to
your lookup value. So with a lookup value of 2 you'll get A instead of B,
which is incorrect anyway.

What you can do is use a combination of INDEX and MATCH functions. So if
your lookup values are in column C, you can try:
=INDEX($B$1:$B$15,MATCH(C1,$A$1:$A$15,-1))
Note: you'll need to reverse sort your original data in columns A and B for
this to work

--
-Simon


"Dave P" wrote:

I can't use VLOOKUP because the 400 or so values that I have do not match the
values in my column A, they are for example 1.3, 5.6 or 27.1

"Ron Rosenfeld" wrote:

On Fri, 28 Mar 2008 08:40:03 -0700, Dave P <Dave
wrote:

is there an alternative to using IF, as I believe one is limited to 7 IF
statements in a formulae.

My scenario is;

Column A contains 15 different values, let's say
1,3,5,7,9,11,13,15,17,19,21,23,25,27,29
Column B has corresponding Letters A,B,C,D,E,F,G,H,I,J,K,L,M,N,O

I would like to run a formula against approx 400 numbers, (most between 0 &
29) and return the corresponding letter

ie
IF A1 <=1,A,IF A1 <=3,B etc etc - obviously I can't use IF because I am
limited to 7 IFs.

Any suggestions greatly appreciated !!

Use VLOOKUP

--ron