Value of sheet A cell = sheet B cell => fill in sheet name

[AA Arens]

I have sheet A and three sheets N1, N2, N3

How to have a value "name of sheet" filled in a cell in sheet A when a
value in a cel in A and a cell in one of the sheets N1, N2, N3 is the
same?

Sheet A
10 | |
11 | | X
12 | |

Sheet N1
...
...
...

Sheet N3
...
11
...

X -> N3

I cound't find it out via the Help section.


Bart

 

[Max]

Assume data to be compared in sheets: N1, N2, N3 are in col A. Data is
assumed unique in each sheet and across all 3 sheets, ie there are no
duplicates

Then in sheet: A, with the data in A1 down,
Put in B1:
=IF(ISNUMBER(MATCH(A1,'N1'!A:A,0)),"N1",IF(ISNUMBER(MATCH(A1,'N2'!A:A,0)),"N2",IF(ISNUMBER(MATCH(A1,'N3'!A:A,0)),"N3","")))
Copy B1 down

 

[Dave Peterson]

One way in B1 of sheet A:

=IF(COUNTIF('N1'!A:A,A1)>0,"N1","")
&IF(COUNTIF('N2'!A:A,A1)>0,"N2","")
&IF(COUNTIF('N3'!A:A,A1)>0,"N3","")

(That's one formula in a single cell)

If the value shows up in all 3 sheets, you'll see N1N2N3.

If you only want the first to show up:
=IF(COUNTIF('N1'!A:A,A1)>0,"N1",
IF(COUNTIF('N2'!A:A,A1)>0,"N2",
IF(COUNTIF('N3'!A:A,A1)>0,"N3","")))

 

[AA Arens]

Both solutions offered works, but it needs a few seconds calculating
time. Is the a way to speed it up?

Bart

 

[Dave Peterson]

If you have lots of data, then Max's formula with =match() should be quicker.