You are here:

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.

Since it works for me, that is the best I can offer.

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2017 All rights reserved.