How do I nest more then 7 IF's?

[KenCanuck]

I need to nest about 13 IF functions in one formula, can I do this?

 

[Bob Phillips]

If you don't have Excel 2007, you can't. If you do have Excel 2007, you
shouldn't.

Tell us what you are trying to do, in words, there is bound to be a better
way.

 

[M Kan]

Are you trying to use this to catagorize a data set or summarize by several
criteria? As far as I know, you can't nest more than 7 IF statements.

 

[KenCanuck]

Essentially I have a row of 13 dollar figures that I need to do a formula
that says =if(k1=a1,a2,if(k1=b1,b2,if(k1=c1,c2... Is there another way to do
that?

 

[KenCanuck]

HI Bob, essentially I have a row of 13 dollar figures, and my formula goes
like this... =if(k1=a1,a2,if(k1=b1,b2,if(k1=c1,c2,... Another way to do
that where I don't nest 13 IF's?? Thanks

 

[Bob Phillips]

=INDEX(A2:M2,,MATCH(Sheet1!K1,A1:M1,0))

 

[Don Guillett]

Look in the help index for VLOOKUP

 

[M Kan]

I'd set up a VLOOKUP table that has your 13 values in one column (A) and the
corresponding values in the next column (B). I tend to group all of these on
a separate worksheet called LOOKUP values. This also let's you add more
values later. Assuming the first value you want to look up is in cell A1,
then your formula will look like this:

=VLOOKUP(A1,LOOKUP VALUES!A:B, 2, FALSE)

 

[KenCanuck]

That's the one! Thanks!!!

=INDEX(A2:M2,,MATCH(Sheet1!K1,A1:M1,0))

--
__________________________________
HTH

Bob

 

[Lorne]

HI Bob, essentially I have a row of 13 dollar figures, and my formula goes
like this... =if(k1=a1,a2,if(k1=b1,b2,if(k1=c1,c2,... Another way to do
that where I don't nest 13 IF's?? Thanks

Look at the HLookup formula. using the range A1..J2 it will find a value in
row 1 and return the value from the same col in row 2.

 

[RMarquez]

I understand that there must be a better way, but I'm not sure how. I'll
explain to you what I'm trying to do, please let me know if you can help.

I have worksheet 1 where I enter 9 rows of data, 4 columns of info.
This data is translated into worksheet 2, which prioritizes the 9 rows based
on the 4 column criteria.
Now I'm on worksheet 3. I would like to pull the data from worksheet 2,
after it's been prioritized and relist in on worksheet 3 in order of
importance 1 through 9.

But I can only nest 7 IF's, not 9. So IF any of worksheet 2 cells a1-a9
have a "1" in it, enter the corresponding worksheet-2, column-B data into
Worksheet 3's B1 cell.

Is this clear? Please let me know if you can help. Thanks!

 

[David Biddulph]

=INDEX(Sheet2!B$1:B$9,MATCH(ROW(),Sheet1!A$1:A$9,0)) in sheet 3 B1, & copy
down ?

 

[JB]

I have a sheet where I need to asign points based upon a finish of a race.
The race finsh place # will be in one colum and I want a formula to
automatically figure the points based upon finish. I was using an IF formula
but I need this to go down up to 20 places and it will not. Can you help me??

 

[Roger Govier]

Hi

On say sheet2, enter 1 to 20 in A1:A20
Enter the points associated with each position in B1:B20
On the sheet where you need to assign the points, assuming it is A1 on
Sheet1, enter in B1
=IF(A1="","",VLOOKUP(A1,Sheet2!A:B,2,0))
Copy down as required