Embedded IF's: more than 7

[VFLong]

I need to have more than 7 embdded IF statements, which excel will not allow. Is there an easy work-around? The IF statements are straight forward =IF($F$25="JOE",A1,IF($F$25="JOHN",B1, etc., etc.). I just happen to have 12 guys (Joe, John, Bob, Dave, Bill, etc., etc.) and I need to embed 12 If statements.

The Excel Help states to use the LOOKUP function, but I can't get that to work.

Any help would be greatly appreciated. Thanks.

 

[Norie]

How did you try the LOOKUP function?

Do you have a list of the names and whatecer you want to return for each name?

 

[Oaktree]

Welcome to the board.

Try:

=INDEX($A$1:$L$1,MATCH($F$25,{"Joe","John","Bob","Dave","Bill", ...},0))

 

[VFLong]

RE: Imbedded IF's

Thanks for the feedback: I tried using lookups in the following manner:

Lookup($F$25,{"JOE","JOHN","BOB", "DAVE", "BILL"},{A1,B1,C1,D1,E1})

I received an error. I think the fact that I was using cells in the result portion rather than actual values tripped it up.

I will give your index/match function a try. Many thanks.

 

[Oaktree]

You cannot use Cell references within { }'s

You could have tried:

=INDIRECT(LOOKUP($F$25,{"JOE","JOHN","BOB","DAVE","BILL"},{"A1","B1","C1","D1","E1"}))

But, I believe INDEX/MATCH is preferred.

 

[VFLong]

RE: Imbedded IF's

I'll give them both a try. I guess there is no way to "trick" excel by somehow combining 2 sets of imbedded IF statements and linking them with an "AND" or "OR"? Again, appreciate the help.

 

[tusharm]

Re: RE: Imbedded IF's

It is much better practice to put the information in a worksheet and just use a LOOKUP. Hiding data inside of formulas that operate on the data is a sure shot way to make debugging, understanding, and maintenance more difficult.

Suppose your data are in A1:B4 with A containing the names and B containing the corresponding desired values. Also suppose the value to be looked up is in D2. Then, use the formula =VLOOKUP(D2,A1:B4,2,FALSE).

Better yet, change the formula to =VLOOKUP(D2,A:B,2,FALSE) and you can add/delete names from the table as required and the worksheet will continue to work correctly!

consider is a named formula that refers to the in-use range. For examples see
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

I'll give them both a try. I guess there is no way to "trick" excel by somehow combining 2 sets of imbedded IF statements and linking them with an "AND" or "OR"? Again, appreciate the help.

 

[Nimrod]

=VLOOKUP(F25,{"Tom","A1";"****","B1";"Harry","C1"},2,FALSE)