How to identify column position based on a lookup value

[donaldg]

I will be working with an array of data approx 500 rows by 50 columns (eg Sheet1!$A$1:$CV$500) in which I will need to check if approx 1000 unique values exist and in which column they appear in. Each unique value should not appear in more than 1 column. Once identified, I need to return the header value at the top of the relevant column.

My thoughts on approach would be perform a lookup of some sort to identify the column number and then use that within an index formula to return the header value based on the column number.

However, I'm not sure how to perform a lookup to get the column number. A match formula can, as far as I am aware, only lookup against a single column or single row.

Any help would be appreciated.

 

[Domenic]

Assuming that A1:CV1 contains the column headers, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX(Sheet1!$A$1:$CV$1,SMALL(IF(Sheet1!$A$2:$CV$500=Sheet2!A2,COLUMN(Sheet1!$A$2:$CV$500)-COLUMN(Sheet1!$A$2)+1),1))

...where Sheet2!A2 contains the unique value.

 

[donaldg]

Genius - works a treat.

Thanks - I'll now try and work out what each part of the formula is doing.

 

[Domenic]

You're very welcome! Thanks for the feedback!