Nested IF / VLOOKUP loops searching multiple worksheets

[jouno]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Job#[/TD]
[TD]Name[/TD]
[TD]Type[/TD]
[TD]Stage[/TD]
[/TR]
[TR]
[TD]1010[/TD]
[TD]Elm Ranch[/TD]
[TD]Residential[/TD]
[TD]Design[/TD]
[/TR]
[TR]
[TD]1012[/TD]
[TD]Oak Park Dev.[/TD]
[TD]Non-Residential[/TD]
[TD]Design[/TD]
[/TR]
[TR]
[TD]1015.5[/TD]
[TD]Beechwood Estates[/TD]
[TD]Mixed Use[/TD]
[TD]Planning[/TD]
[/TR]
</tbody>[/TABLE]

I have worksheets spanning the last 6 years detailing jobs. The earlier (2013-2015) worksheets don't have type and stage, which I need. I've added columns and tried to fill them in using the more recent data with:
//searching for type
=VLOOKUP(A9,'[Worksheet2016]Projects'!$A$7:$D$135, 3, FALSE)

which works for a single worksheet, but I would like to make nested IFs to search all 3 recent worksheets. The logic is like:
IF (worksheet2016 returns a value, good, if not)
ELSEIF (worksheet2017)
ELSEIF (worksheet2018)
Else "not found"

I'd like to do this with Excel code if possible, not VBA. Not sure of the Excel syntax.
Based on earlier searches through this forum, I tried:

//full pathname elided
=IF(VLOOKUP(A8,'worksheet2016'!$A$7:$D$135,3,FALSE)=A8,A8,IF(VLOOKUP(A8,'worksheet2017'!$A$7:$D$135,3,FALSE)=A8,A8,IF(VLOOKUP(A8,'worksheet2018'!$A$2:$D$157,3,FALSE)=A8,A8,"not found")))
but that only returns #N/A

 

[Nine Zero]

try this out

=IF(NOT(ISNA(VLOOKUP(A8,worksheet2016!$A$7:$D$135,3,FALSE))),A8,IF(NOT(ISNA(VLOOKUP(A8,worksheet2017!$A$7:$D$135,3,FALSE))),A8,IF(NOT(ISNA(VLOOKUP(A8,worksheet2018!$A$2:$D$157,3,FALSE))),A8,"not found")))

the reason why yours was not working is because you are not searching in a single column... if you were yours would have been fine but since you are returning a value from column D ... D8 will never equal A8 ... sorta lol

 

[jouno]

Thanks! Not quite what I was looking for, as it pops the job number into the type column, but better than all those #N/As.
I'm trying to collect data from columns 3 and 4 (not necessarily both at once) to put into worksheets that lack that data. Jobs get added, deleted, and changed in status from year to year. Maybe 50% of the blanks can be filled in with this formula.
How is your formula retrieving/displaying the job number and not the type?