Lookup for a specific text read array and return value to summary sheet

[amolvijay]

Dear Experts,
I have a big excel workbook comprising of sales data for various sales person. Each sales person's data is maintained in separate sheet in the master workbook. At the end of sales date worksheet have the detailed data for every sales person. I want to create a summary of detailed data from each sales person in a SUMMARY tab. Detailed data is located in same column for all the sales persons, however the row position is different depending upon the sales data.

Detailed Data for every sales person
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sale (Item A)[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]Sale (Item B)[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]Total Sales ($)[/TD]
[TD]1200[/TD]
[/TR]
</tbody>[/TABLE]

Expected summary
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Sale (Item A)[/TD]
[TD]Sale (Item B)[/TD]
[TD]Total Sales ($)[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]60[/TD]
[TD]70[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sam_D_Ben]

Something like this.

Excel 2013/2016
	A	B	C	D
1	Sales Person	Sale (Item A)	Sale (Item B)	Total Sales ($)
2	John	80	90	1200
3	Richard	60	70	1500
4	Mike	20	40	800
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP($B$1,John!$A$1:$B$3,2,0)
B3	=VLOOKUP($B$1,Richard!$A$1:$B$3,2,0)
B4	=VLOOKUP($B$1,Mike!$A$1:$B$3,2,0)
C2	=VLOOKUP($C$1,John!$A$1:$B$3,2,0)
C3	=VLOOKUP($C$1,Richard!$A$1:$B$3,2,0)
C4	=VLOOKUP($C$1,Mike!$A$1:$B$3,2,0)
D2	=VLOOKUP($D$1,John!A1:B3,2,0)
D3	=VLOOKUP($D$1,Richard!A2:B4,2,0)
D4	=VLOOKUP($D$1,Mike!A3:B5,2,0)

 

[amolvijay]

Thank you Sam_D_Ben. My DETAILED DATA in all the sheets is in the same column but row position is different. I wish a formula to read the Sales person name from Summary sheet and return the required sales data from individual DETAILED DATA.

 

[Sam_D_Ben]

For example, you can change the rows in this formula

=VLOOKUP($B$1,John!$A$1:$B$3,2,0), if the data is in another row like A12:B15, you can edit it as =VLOOKUP($B$1,John!$A$12:$B$15,2,0)

This is just an example. You can edit the row.