VLOOKUP in 2 Tabs

[TriHard]

Hi All,

Hoping you can help with the below. I can't get my head around it but I think there's going to be quite an easy solution-

I currently have a spreadsheet which tracks all of the quotations we do in a year. If the quotation turns into a job, we assign a job number (for which we have another tab in the same spreadsheet). I have added a VLOOKUP formula to automatically add the quote information to a line in the jobs tab which has worked great.

Moving into the new year we are looking to start a new quotes tab solely for 2018 but use the same jobs tab (reason for this is that some of the quotes from 2017 won't become a job until next year so we want them all under 1 tab).

So in a nutshell I currently have the below tabs-

2017, 2018 & JOBS

I use the below VLOOKUP to move any quote from the 2017 tab into the JOBS tab-

=VLOOKUP("01806",'2017'!$A$3:$C$1000,3,FALSE)

My question is-

Can I add the 2018 tab into this formula so that it will look up the job number (in the above example that's "01806") in both the 2017 & 2018 tabs?

Any help really would be appreciated. Cheers

 

[Marcelo Branco]

One way...

=IFERROR(VLOOKUP("01806",IF(ISNUMBER(MATCH("01806",'2017'!$A$3:$A$1000,0)),'2017'!$A$3:$C$1000,'2018'!$A$3:$C$1000),3,FALSE),"Not found")

M.