Vlookup across multiple tabs

[milesorru]

Hi

I'm sure the answer to this is very simple.

I have a list of barcode numbers on one tab in column B. This barcode may then appear on another tab in column B as it may be returned. The different tabs are return centres. Lets say "Return 1", "Return 2", "Return 3". The easy thing to do would be to have "Return 1" etc running alongside the barcodes in the other tabs to return something but I cannot do that.

If it appears in 'Return 1' tab, I need it to return "Return 1". If it appears in 'Return 2', "Return 2" etc...

Many Thanks in advance

 

[Robert Mika]

If you can added the Sheet name next to barcode(like this)
Excel 2010

	A	B	C
1
2
3		11111	Return 1
4
5
6
7
8
9
10
11

<tbody>
</tbody>

Return1​

you can then use:
Excel 2010

	A	B	C
1		11111	Return 1
2
3
4
5
6

<tbody>
</tbody>

Summary​

Array Formulas Cell Formula C1 {=VLOOKUP(B1,INDIRECT("Return"&MATCH(TRUE,COUNTIF(INDIRECT("Return"&ROW(INDIRECT("1:10"))&"!b2:b100"),B1)>0,0)&"!b2:c100"),2,0)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>