lookup in different sheet

[dheeraj27]

I have around 12 datasheets in one workbook, so in 13th sheet, I want to put a unique value which should look up for that value in other 12 tabs column D and if it matches, then return column A info in that tab.


Example in 12 tab Data is like

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]first name[/TD]
[TD]Last name[/TD]
[TD]mobile[/TD]
[/TR]
[TR]
[TD]1/1/2018[/TD]
[TD]Dheeraj [/TD]
[TD]Kumar[/TD]
[TD]123456852[/TD]
[/TR]
[TR]
[TD]1/15/2018[/TD]
[TD]Rahul [/TD]
[TD]Arora[/TD]
[TD]987654258[/TD]
[/TR]
</tbody>[/TABLE]

So in 13th sheet if enter mobile : - 987654258, it should give me date 1/15/2018. Matching in all 12 tabs.

Thanks

 

[Aladin Akyurek]

Let SheetList refer to a range which houses one by one of all 12 sheets.

Let A2 of the 13th sheet house 987654258.

​

In B2 of the 13th sheet, control+shift+enter, not just enter:

=VLOOKUP($A2,CHOOSE({1,2},INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,CHOOSE({1},COUNTIFS(INDIRECT("'"&SheetList&"'!D2:D100"),$A2)>0,0)))&"'!D2:D100"),INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,CHOOSE({1},COUNTIFS(INDIRECT("'"&SheetList&"'!D2:D100"),$A2)>0,0)))&"'!A2:A100")),2,0)