Too Many Conditions for IF Function - Help Please!

[Pam]

I need to search my spreadsheet with the IF function below for 10
salespeople. With the IF function, I'm limited to 7. I've researched
VLookup, Choose, etc. and cannot figure out how to make anything else work
for situtation.

=IF($H$87="BP",F$90,IF($H$87="BB",C$90,IF($H$87="BB",C$90IF($H$87="WB",C$90,
IF($H$87="HC",C$90, IF($H$87="DP",C$90, IF($H$87="DW",C$90,
IF($H$87="TD",C$90, IF($H$87="HM",C$90IF($H$87="EB",C$90,0))))))))))

If anyone has solution, I'd greatly appreciate it.
Thanks in advance.
Pam

 

[Don Guillett]

Modify this idea to suit
=IF(H87="bp",1,IF(OR(H87={"bb","wb"}),2,3))

 

[Harlan Grove]

=IF($H$87="BP",F$90,IF($H$87="BB",C$90,IF($H$87="BB",C$90IF($H$87="WB",C$90,
IF($H$87="HC",C$90, IF($H$87="DP",C$90, IF($H$87="DW",C$90,
IF($H$87="TD",C$90, IF($H$87="HM",C$90IF($H$87="EB",C$90,0))))))))))

....

This looks like it should return the value of cell F90 only if H87
equals "BP", the value of cell C90 if H87 equals any other expected
value, and zero otherwise. If so, you only need 2 IF calls.

=IF($H$87="BP",F$90,IF(OR($H$87=
{"BB","BB","WB","HC","DP""DW","TD","HM","EB"}),C$90,0))

Note that you seem to have 2 different individuals identified by BB.
That may prove to be a problem.

If you actually need different cells in row 90 for different
individuals, you could use a 2-way lookup like

=IF(OR($H$87={"BB","BP","DP""DW","EB","HC","HM","TD","WB"}),
INDEX(C$90:Z$90,LOOKUP($H$87,
{"BB","BP","DP""DW","EB","HC","HM","TD","WB"},
{1,4,x,y,z,...}),0)

 

[Per Jessen]

Hi Pam

Based on your formula, I think this should do it. In your formula you have B
twice, I assume it is a typo and leave it to you to insert the correct
initials.

=IF(H87="BP",F90;IF(OR(H87="BB",H87="WB",H87="HC",H87="DP",H87="DW",H87="TD"),C90,0))

Hopes this helps.
....
Per

 

[Luke M]

It appears all but "BP" return cell C90. (Also, why check for "BB" twice?)
Why not use:

=IF($H$87="BP",F$90,IF(ISNUMBER(SEARCH($H$87,"BB WB HC DP DW TD HM
EB")),C$90,0))

If you don't want to use the SEARCH function, you could use OR, and just
list all your arguements
OR(H87="BB",H87="WB",...,etc)

 

[Pam]

I'm sorry - I was using the first two conditions to see if situation would
actually work and copied it for the additional 8 times needed and forgot to
change the cells to be returned.

Thank you all for your help, but this is the way it should actually read,
picking up value in the next third column for each salesperson.

=IF($H$87="BP",F$90,IF($H$87="BB",C$90,IF($H$87="BV",I$90,
IF($H$87="WB",L$90, IF($H$87="HC",O$90, IF($H$87="DP",R$90,
IF($H$87="DW",U$90, IF($H$87="TD",X$90,
IF($H$87="HM",AA$90IF($H$87="EB",AD$90,0))))))))))

Again, my apologies.
Pam

 

[Luke M]

Since your cells that you're returning from our 3 apart, can use that to our
advantage.

=IF(ISNA(MATCH($H$87,{"BB","BP","BV","WB","HC","DP","DW","TD","HM","EB"},0)),0,OFFSET(C$90,0,-3+3*MATCH($H$87,{"BB","BP","BV","WB","HC","DP","DW","TD","HM","EB"},0)))

To explain: Formula first checks if value is present. If not, return zero.
Else, find value within given array. Multiply result by 3 to give the correct
spacing needed, and then offset from starting point (C90). Note the -3 change
1*3 back to 0, aka our starting point.

 

[Harlan Grove]

=IF($H$87="BP",F$90,IF($H$87="BB",C$90,IF($H$87="BV",I$90,
IF($H$87="WB",L$90,  IF($H$87="HC",O$90, IF($H$87="DP",R$90,
IF($H$87="DW",U$90, IF($H$87="TD",X$90,
IF($H$87="HM",AA$90IF($H$87="EB",AD$90,0))))))))))

....

=IF(OR($H$87={"BB";"BP";"BV";"DP";"DW";"EB";"HC";"HM";"TD";"WB"}),
INDEX(C$90:AD$90,VLOOKUP($H$87,{"BB",1;"BP",4;"BV",7;"DP",16;"DW",19;
"EB",28;"HC",13;"HM",25;"TD",22;"WB",10},2)),0)

 

[Pam]

Luke,

Excellent! It worked perfect!! Thank you for your help and explanation.

Would you mind helping with another copy problem that I think OFFSET might
work for, but I can't seem to get it to come together. This is the first
time I've used OFFSET.

I have quite a bit of data that I want to link to from another ss.
Original SS is laid out as such:

Dept Jan Feb Mar...
Units cellB48 cellC48 cellD48
Parts cellB50 cellC50 cellD50
Repairs cellB49 cellC49 cellD49

I want to link to cells above in this order
Month Units Parts Repairs
Jan =cellB48
Feb
Mar

How can I copy Jan Units entry (=cellB48) across row and down each column?
The only reason I ask is because I have the 10 slsp as the Match in previous
problem and each has the 3 column segment for Units, Parts, Repairs.

Thanks again and in advance for your help
Pam

 

[Pam]

Thank you, Harlan

=IF($H$87="BP",F$90,IF($H$87="BB",C$90,IF($H$87="BV",I$90,
IF($H$87="WB",L$90, IF($H$87="HC",O$90, IF($H$87="DP",R$90,
IF($H$87="DW",U$90, IF($H$87="TD",X$90,
IF($H$87="HM",AA$90IF($H$87="EB",AD$90,0))))))))))

....

=IF(OR($H$87={"BB";"BP";"BV";"DP";"DW";"EB";"HC";"HM";"TD";"WB"}),
INDEX(C$90:AD$90,VLOOKUP($H$87,{"BB",1;"BP",4;"BV",7;"DP",16;"DW",19;
"EB",28;"HC",13;"HM",25;"TD",22;"WB",10},2)),0)