Finding cell location of a value in a multi column array

I need to find a solution using Function and not code. In an array of multiple columns each with multiple rows of values, I need to find the location/cell of a value so that I can retrieve the column heading.

As a small example below if I have value C3, I want to find that the value is in column 3 row 3 of the array so I can extract heading Y. But if a value K9 is searched for it needs to results in an error

W X Y Z
A1 B1 C1 D1
A2 B2 C2 D2
A3 B3 C3 D3
A4 B4 C4 D4

I have tried 'Match' but that only works on a single column array. 'Lookup' will provide a heading but it will also provide a heading even if the value isn't found.

Any suggestions would be appreciated. Thank you

something like this???
i think i got what you want

Thank you Ford the combination of Countif and Index with helper row lets me pull the header for the value entered. The values are long text but I used A1 etc to make the example easier to read.

Just in case you could help again, my next challenge is that the real table I have is actually 10 columns with 100 rows maximum but each column has less than 100 text values. I have to combine all values into 1 column to be used in a data validation so I need to eliminate all the blank cells in that combination. Using my array of values below I would need to build a column with A1, B1, B2, B3, C1, D1, D2 to present for data validation and whatever value is selected I would use your earlier help to present them with the heading.

A1 B1 C1 D1
B2 D2
B3

Thank you Tony for an alternave 2 way to get results

Bob Ptacek

Originally Posted by FDibbins

You have shown what your sample data looks like...


What would your end result look like?

Unfortunatly the copy of the samle didn't line up right. It was to be 4 columns. First with 1 A, 2nd with 3 Bs 3rd with 1 C and 4th with 2 Ds. The column I need to build from that array would have the values A1, B1, B2, B3, C1, D1, D2. So that after A1 when a blank cell is encountered, the functions need to move to teh next column in the array. With offset, indirect, index etc I can go through the array but I take teh blanks with me. What I'm building is a column to be used in data validation and I need to get eh blanks out.