IF stmt with multiple results

[Guest]

Is there a way to populate more than one cell when a condition is TRUE?

I have an nested IF stmt that checks 1 cell.

=IF(K2="AB",F2-8,IF(K2="AC",F2-7,IF(K2="AD",F2-7,IF(K2="AK",F2-7,IF(K2="AR",F2-7,IF(K2="BP",F2-7,IF(K2="BV",F2-7,IF(K2="BW",F2-7,""))))))))

In English, look at K2 and, depending on the 2-digit code, subtract x from
F2. (I have many more, but am limited to 7 nested stmts.) This works quite
well, with the results going to a blank cell, M2.

The problem is this: I need to populate cells N and O, too. If K2 = AB, for
example, subtract 8 from F2, but ALSO populate the acct exec's name as
N2="last name", O2="first name". Fortunately, these are one-to-one: each
code will only ever have one acct exec and I can hard-code them.

Possible?

Karen F

 

[Pete_UK]

Karen,

You could simplify your formula, as well as being able to extend it to
take in more values, and accomplish your latest task by means of
VLOOKUP. You need to set up a table somewhere (eg W1:Z8) like this:

AB 8 surname1 forename1
AC 7 surname2 forename2
AD 7 surname3 forename3
AK 7 surname4 forename4
AR 7 surname5 forename5
BP 7 surname6 forename6
BV 7 surname7 forename7
BW 7 surname8 forename8

Then your multi-if formula in M2 becomes:

=IF(ISNA(VLOOKUP(K2,W$1:Z$8,2,0)),"",F2-VLOOKUP(K2,W$1:Z$8,2,0))

If your table were to extend to 20 rows, all you would need to do
change the $8 to $20 (twice) in the formula.

In N2 you can enter this formula:

=IF(M2="","",VLOOKUP(K2,W$1:Z$8,3,0))

to get the appropriate surname. In O2 enter this formula:

=IF(M2="","",VLOOKUP(K2,W$1:Z$8,4,0))

to bring across the appropriate first name, obviously changing $8 in
both formulae to suit your table.

Hope this helps.

Pete

 

[Guest]

To add to Pete's reply:

If you made your VLOOKUP table a dynamic range, you wouldn't need to change
your formulae if data was added/deleted.

See for info on Dynamic Ranges:

http://www.contextures.com/xlNames01.html#Dynamic

So using Pete's example, I created a named range called "Exectab" with the
following defintion:

=OFFSET(Sheet2!$W$1,,,COUNTA(Sheet2!$W:$W),4)

and your VLOOKUP would become:


=IF(ISNA(VLOOKUP(K2,Exectab,2,0)),"",F2-VLOOKUP(K2,Exectab,2,0))


HTH

 

[Guest]

Try this:

=IF(K2="AB",F2-8,IF(OR(K2={"AC","AD","AK","AR","BP","BV","BW"}),F2-7,""))

 

[Guest]

Teethless, this doesn't solve my original problem but this solution is SO
MUCH BETTER than what I was doing and does the work of 8 columns in one.
I've been revamping my spreadsheets like crazy and haven't been able to thank
you until now.