Search a number in multiple tabs and show up the tab name

sureshnov · Feb 6, 2019

A1 has some number, search the number on tab "A","B","C" and "D" . then display the tab name in B1 which has the A1 value .

Any idea?

navic · Feb 6, 2019

Lookup value across multiple sheets and return sheet tab

sureshnov said:
display the tab name in B1

Try this Array formula

Code:

=IFERROR(INDEX(AllSheets;1/(1/SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&AllSheets&"'!A1:A10");$A2));ROW(INDIRECT("1:"&COUNTIFS(AllSheets;"?*"))));COLUMN(A2))));"")

For details please see this tutorial.

Search a number in multiple tabs and show up the tab name

sureshnov

New member

navic

New member

Attachments