How to lookup values across multiple sheets and return which sheet it's on

[finch_tiff]

Hi,

I am new to formula's. I only know the basics. I have a spreadsheet of our work program, the first tab has a list of all our sites we cover and then there are tabs along the bottom with all the teams and the sites are in the teams linked with reference numbers. I would like to recall the tab name on to the front sheet, so you can see at glance what team the sites are in. But the reference numbers I want to search are in two columns only, I don't want it to search the whole sheet. Is this possible. Please see attached picture for more information.

I have the below formula in at the moment, which is searching just column A on the teams tab, but I need it to search column A and column K only. Is this possible? Does that make sense? I'm not sure if I've explained that clearly.

=IFERROR(INDEX(Lookup_sheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&Lookup_sheets1&"'!A:A"),A2)>0,0)),"NA")

 

[Domenic]

Try...

=IFERROR(INDEX(Lookup_sheets1,MATCH(TRUE,(COUNTIF(INDIRECT("'"&Lookup_sheets1&"'!A:A"),A2)+COUNTIF(INDIRECT("'"&Lookup_sheets1&"'!K:K"),A2))>0,0)),"NA")

Hope this helps!