# Embedded IF's: more than 7

#### VFLong

##### New Member
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.

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
How did you try the LOOKUP function?

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

Welcome to the board.

Try:

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

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.

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.

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.

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!
VFLong said:
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

VFLong said:
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.

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

Replies
0
Views
140
Replies
6
Views
364
Replies
1
Views
118
Replies
3
Views
108
Replies
0
Views
398

1,196,073
Messages
6,013,264
Members
441,758
Latest member
Abren

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back