Excel/multiple if statements with Vlookup
I am creating a spreadsheet to allocate commissions for private piano instruction. There are three piano teachers: Harry, Susan, and Jay. Though each teacher either does a 30 min lesson, a 45 min lesson or an hour lesson, they are NOT paid the same amount per lesson because they have different experience levels. So, for example, Harry earns $20 for a 30 min lesson, while Jay earns $25. Because the commissions differ by sales person but are fixed (ie not a percentage), I created three lookup tables: one for each salesperson. I have validated that each vlookup table works.
I am now trying to string together multiple "if" statements for my workbook so that I can tabulate their paychecks. The statement I have written is:
=IF(B2="harry",VLOOKUP(F2,'Lookup Table '!$A$2:$B$8,2,FALSE), IF(B2="jay",VLOOKUP(F2,'Lookup Table '!$A$21:$B$27,2,FALSE), IF(B2="susan",VLOOKUP(F2,'Lookup Table '!$A$12:$B$18,2,FALSE))))
BUT, the only commission that looks up correctly is if the "teacher name" is set to Jay. If it is set to Harry or Susan, I get a "false" statement. This is true whether I move the "jay" "if" statement to first or to the last of the three "if" statements.
I have validated that the cell references are shifting appropriately with rows (ie, row 3 is reading IF(B3...)).
I have validated that when I have a single if statement, the lookup tables work fine. So, in other words, when the only statement is =IF(B2="harry",VLOOKUP(F2,'Lookup Table '!$A$2:$B$8,2,FALSE) I get Harry's commission numbers.
Any thoughts or alternate approaches?
Sorry for the late response. My internet connection has been down for over 24 hours due to a cut line.
I tested your formula and it worked fine for me. (Excel 2010, Windows 7)
I tested with all three names in the same locations you show and the formula worked as expected.
if you want to send me your workbook, I can test it. email@example.com
Since it works for me, that is the best I can offer.