Find Value in Table and Return Column Header for Each Occurrence

[ers42103]

I recently posted this thread and I got the answer that I needed. However, I need to expand this to pull that information from 9 different sheets. Is this possible?

The array formula is:
=IFERROR(INDEX($A$1:$E$1,SMALL(IF($A$2:$E$4=$G1,COLUMN($A1:$E1)),COLUMNS($H1:H1))-COLUMN($A1)+1),"")

The sheet names are :
abc
def
ghi
jkl
mno
pqr
s
tuv
wxyz

[Tony Valko]

You would have to use a separate formula for each individual sheet.

[ers42103]

Then would there be a way to combine the information on the 9 separate sheets into one sheet to have one master list?

[Tony Valko]

I guess that would depend on how you have the data organized.

[ers42103]

Its like this

Title name1 name2
Title name1 name3 name4

For each sheet mentioned above. I need a summary page that would have the title and all the names, from all 9 sheets, listed next to it. There are 439 titles and 148 Names.

[Tony Valko]

I'm not sure what you're attempting to do but with data on several sheets here's how I would do a summary:

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	abc	Results from abc sheet	abc1	abc2	abc3
3	def	Results from def sheet	def1
4	ghi	Results from ghi sheet	ghi1	ghi2
5	jkl	Results from jkl sheet	jkl1
6	mno	Results from mno sheet	mno1	mno2	mno3
7	pqr	Results from pqr sheet	pqr1	pqr2
8	s	Results from s sheet	s1	s2	s3
9	tuv	Results from tuv sheet	tuv1
10	wxyz	Results from wxyz sheet	wxyz1	wxyz2	wxyz3

[ers42103]

I have 9 sheets of customers that are separated alphabetically, each section of the alphabet having its own sheet because of the amount of information. On this sheet someone selects what title the customer wants with there being 439 options. What I am trying to do is create a master list that updates from these 9 sheets based on the selection. The information needs to be displayed with the 439 titles down column a then each customer that wants that specific title(which can be on any of the 9 sheets) in the cells to the right.

So if necessary I could pull the information from each selection sheet separately, then the combine the information or I need a way to pull the information from all the sheets to display on one page. Like this:

Title 1 abc1 wxyz2
Title 2 def2 def3 mno1
Title 3 abc26 s1 mno1
ect.

This worked for one page of information but I cannot seem to get it to work for having 9 pages.

[Tony Valko]

I don't think this can be done with a single formula to extract data from multiple sheets.

If it can be done with a single formula I don't know how to do it!