Combining vlookup formulas

[Rain78]

I have an Excel workbook with 3 sheets. Sheet 1 has a list of all tasks that need to be completed in a month. Sheets 2 and 3 are a breakout of Sheet 1, so each task appears on either 2 or 3. In the first column are the task numbers, and then we fill in a date when a task is complete. Otherwise it stays blank.

On Sheet 1 I would like to do a vlookup function to pull in the date that a task was complete. The tricky part is, if the task is not on Sheet 2, I want it to look at Sheet 3. If no complete date has been entered on either 2 or 3, I want it to come back with "Not Complete." I can do all of the functionality of the above just looking at Sheet 2, but I don't know how to add Sheet 3 to the mix.

Thanks!

[Sam Capricci]

Can you use an iferror VLOOKUP for sheet 2 that if it returns an error goes to sheet3?

[ChemistB]

=IFERROR(VLOOKUP(A2, Sheet2!$A$1:$F$1000, 2, FALSE), IFERROR(VLOOKUP(A2, Sheet3!$A$1:$F$1000, 2, FALSE), "Not Complete")))

Something like that

[Sam Capricci]

=IFERROR(VLOOKUP(A2, Sheet2!$A$1:$F$1000, 2, FALSE), IFERROR(VLOOKUP(A2, Sheet3!$A$1:$F$1000, 2, FALSE), "Not Complete")))

Something like that

Yeah, something like that would have been mine if I wasn't too lazy to type it on my ipad

[Rain78]

That makes sense but I'm getting a "too many arguments" error. Thanks for all of your help so far!!

=IFerror((VLOOKUP(A24,Hospital!$A$7:$B$119,2,FALSE)),iferror(VLOOKUP(A24,Professional!$A$7:$B$124,2,FALSE)),"Not Complete")

[ChemistB]

=IFerror(VLOOKUP(A24,Hospital!$A$7:$B$119,2,FALSE), iferror(VLOOKUP(A24,Professional!$A$7:$B$124,2,FALSE),"Not Complete")))

[Tony Valko]

That makes sense but I'm getting a "too many arguments" error. Thanks for all of your help so far!!

=IFerror((VLOOKUP(A24,Hospital!$A$7:$B$119,2,FALSE)),iferror(VLOOKUP(A24,Professional!$A$7:$B$124,2,FALSE)),"Not Complete")

You have too many ( )...

=IFERROR(VLOOKUP(A24,Hospital!$A$7:$B$119,2,0),IFERROR(VLOOKUP(A24,Professional!$A$7:$B$124,2,0),"Not Complete"))

[Rain78]

Ok so that worked - thank you!! But instead of having "Not Complete" it is showing as 1/0/1900 for anything without a date. Is that just a formatting issue?

[Sam Capricci]

Yes, just format to general.

[ChemistB]

The issue has nothing to do with "Not Complete" The problem is that your lookup value is appearing in one of the sheets BUT there's nothing in Column B so it is returning 0. The best was to fix that is to do a custom format
mm/dd/yyyy (or whatever you want);;
Does that work for you?

[Tony Valko]

Ok so that worked - thank you!! But instead of having "Not Complete" it is showing as 1/0/1900 for anything without a date. Is that just a formatting issue?

Having just read the original post, I think that we've all missed the boat on this one!

Assuming that the task will always be on one sheet or the other, try this...

=IF(COUNTIF(Hospital!$A$7:$B$119,A24),IF(VLOOKUP(A24,Hospital!$A$7:$B$119,2,0)="","Not Complete",VLOOKUP(A24,Hospital!$A$7:$B$119,2,0)),IF(VLOOKUP(A24,Professional!$A$7:$B$124,2,0)="","Not Complete",VLOOKUP(A24,Professional!$A$7:$B$124,2,0)))

Format the result as Date

[Rain78]

I tried changing the format to General and it returns a 0 instead of the strange date. I tried what Tony mentioned and that didn't work. Thanks to you all for the suggestions! If you have any others I would love to hear them. I'm so close!! It's just the Not Complete piece.

[ChemistB]

Okay, let's clarify When you want "Not Complete"
If your lookup value is on either of those sheet but there's no date, do you want "Not Complete"? or do you want it to return a blank?

Alternately, the way you initially described it, if your lookup value doesn't appear on either worksheet, then you want a "Not Complete"
or some combination of the above?

[Rain78]

Yes, so if there is not a date filled in on either sheet, I would like it to return Not Complete instead of a blank.

[ChemistB]

Also, "it didn't work" doesn't help us much. It's like taking your car in because the wiper fluid doesn't spray and telling the mechanic, it doesn't work. Does the formula give you an error, if so, what is it? or the wrong result (what's the expected result)? or maybe Excel doesn't even let you enter it. This would help.

[ChemistB]

Okay, that's what Tony's formula was aiming for. The rest of us were on a different planet.

[Rain78]

Sorry, it says #VALUE! with Tony's suggestion.

=IF(COUNTIF(Hospital!$A$7:$B$119,A198),IF(VLOOKUP(Hospital!$A$7:$B$119,2,0)="","Not Complete",VLOOKUP(A198,Hospital!$A$7:$B$119,2,0)),IF(VLOOKUP(A198,Professional!$A$7:$B$124,2,0)="","Not Complete",VLOOKUP(A198,Professional!$A$7:$B$124,2,0)))

[ChemistB]

If you go back to the formula that was giving you 1/0/1900 and select all your cells and do this custom format

mm/dd/yyyyy;;"Not Complete";@

[Tony Valko]

Yes, so if there is not a date filled in on either sheet, I would like it to return Not Complete instead of a blank.

I'm pretty sure that's what the formula in post #11 does.

Here's what it's doing...

It first tests to see if the lookup value is on the Hospital sheet. If it is it then checks for an empty cell. If the cell is empty the formula returns "Not Complete". If the cell is not empty it returns the date.

If the lookup value is not on the Hospital sheet then it does the lookup on the Professional sheet. If the cell is empty the formula returns "Not Complete". If the cell is not empty it returns the date.

The formula (I) assume the lookup value is ALWAYS present, on one sheet or the other.

[Rain78]

ChemistB - you ROCK! Thanks so much for your help.

[Tony Valko]

A word of caution about using the custom format.

Even though the cell may DISPLAY "Not Complete", the true underlying value of the cell is still 0.

So, if you have downstream calculations looking for cells that contain "Not Complete", you may have problems!

[Rain78]

Good point, Tony. I was able to get your formula to work! Thanks again to everyone. I've learned a lot.

[Tony Valko]

Good deal. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.