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.

Name	City	Country	ID
Sam	New York	United States	001
Anna	London	United Kingdom	020
Sean	Oslo	Norway	030

<tbody>
</tbody>

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

Name	RVL Name	RVL City	RVL Country	RVL ID	Sheet Source
Sam	Sam	New York	United States	001	Name1
Anna	Anna	London	United Kingdom	020	Name2
Sean	Sean	Oslo	Norway	030	Name3

<tbody>
</tbody>

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)