VLookup Value across multiple sheets and return sheet name

[unknownymous]

Hi Guys,

I'm having a hard time with this.

I need a code that will look for a value on multiple sheets and return it's sheet name.

So in a workbook I have 4 sheets namely: Raw, Name1, Name2, Name3 & Name4
"Raw" sheet contains names only and as for the other sheets, it has below data.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Country[/TD]
[TD]ID[/TD]
[/TR]
[TR]
[TD]Sam [/TD]
[TD]New York[/TD]
[TD]United States[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]London[/TD]
[TD]United Kingdom[/TD]
[TD]020[/TD]
[/TR]
[TR]
[TD]Sean[/TD]
[TD]Oslo[/TD]
[TD]Norway[/TD]
[TD]030[/TD]
[/TR]
</tbody>[/TABLE]


So in Raw tab, the final data will look like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]RVL Name[/TD]
[TD]RVL City[/TD]
[TD]RVL Country[/TD]
[TD]RVL ID[/TD]
[TD]Sheet Source[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD]Sam[/TD]
[TD]New York[/TD]
[TD]United States[/TD]
[TD]001[/TD]
[TD]Name1[/TD]
[/TR]
[TR]
[TD]Anna[/TD]
[TD]Anna[/TD]
[TD]London[/TD]
[TD]United Kingdom[/TD]
[TD]020[/TD]
[TD]Name2[/TD]
[/TR]
[TR]
[TD]Sean[/TD]
[TD]Sean[/TD]
[TD]Oslo[/TD]
[TD]Norway[/TD]
[TD]030[/TD]
[TD]Name3[/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

 

[Aladin Akyurek]

Create a range in a convenient location, which houses the names of the relevant sheets one by one. Select that range and name the selection SheetList using the Name Box on the Formula Bar.

In F2 of Raw, control+shift+enter, not just enter, and copy down:

=INDEX(SheetList,MATCH(TRUE,COUNTIFS(INDIRECT("'"&SheetList&"'!A2:A100"),$A2)>0,0))

In B2 of Raw just enter and copy down:

=VLOOKUP($A2,INDIRECT("'"&$F2&"'!A:D"),1,0)

In C2 of Raw just enter and copy down:

=VLOOKUP($A2,INDIRECT("'"&$F2&"'!A:D"),2,0)

In D2 of Raw just enter and copy down:

=VLOOKUP($A2,INDIRECT("'"&$F2&"'!A:D"),3,0)

In E2 of Raw just enter and copy down:

=VLOOKUP($A2,INDIRECT("'"&$F2&"'!A:D"),4,0)