Alternative to IF

[Dave P]

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 !!

 

[Ron Rosenfeld]

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

 

[Dave P]

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]

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.

 

[Pete_UK]

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

 

[Dave P]

thanks - I will give both solutions a try

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

 

[SimonCC]

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

 

[Pete_UK]

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