Trouble with nested IF formula

Hi All

I'm in the middle of creating an audit spreadsheet that automatically populates a reference number when you select the month and person's name that you want to audit. The formula references an external worksheet to keep the source information and the "front end" section users will look at apart.

I have separated the months out into sheets within the source workbook, and have managed to get an IF formula to work so when you select one of the months it works, however I can't get it to work for the other months in the same formula if that makes any sense.

My formula so far is:

=IFERROR(IF(C6="December 2018",VLOOKUP(C4&"Service1",[Audits_2018.xlsx]December!$A2:$G200,5,0)),"")

C6 is where you would select the month, C4 is where you would select the person's name, and "Service1" is one of several services the person works on - I'm going to do each one separately.

I am hoping to get the formula to look something like this, where it returns the correct reference number depending on the month/name selected however it isn't working and just returns FALSE with every month except December:

=IFERROR(IF(C6="December 2018",VLOOKUP(C4&"Service1",[Audits_2018.xlsx]December!$A2:$G200,5,0)),IF(C6="November 2018",VLOOKUP(C4&"Service1",[Audits_2018.xlsx]November!$A2:$G200,5,0)),IF(C6="September 2018",VLOOKUP(C4&"Service1",[Audits_2018.xlsx]September!$A2:$G200,5,0))"")) etc.

I'm not very experienced with Excel and I'm at a bit of a loss as to how to get it to work! I've tried taking out and adding parentheses and commas etc. but nothing works, saying I've either got too many or too few arguments in the function.

Any ideas would be greatly appreciated!

You should look at using the indirect function Functions.

Untested because you did not attach a sample worksheet.

=IFERROR(VLOOKUP(C4&"Service1",indirect("[Audits_2018.xlsx]" & C6 & "!$A2:$G200"),5,0)),"")

Like I said. Untested.


Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

=VLOOKUP(C4&"Service 1",INDIRECT("[Data.xlsx]" & LEFT(C6,FIND(" ",C6,1)-1) & "!$A2:$D200"),4,0)