Facing problem with index and Match function

[Padam]

i want to display the column header based on the given value when i am trying to do it with index match combination then its working fine but i am facing problem because my data is having duplicate values then by using match function it returns only the column name of left most first instance where as in next row i want it to return the column header of second instance. i am trying to do it with match and index. Do u have any alternate solution for this problem. For reference i am uploading file and the formula i am using .

Any help in this regard would be greatly appreciable...

 

[NARAYANK991]

Hi ,

See your file now.

Narayan

 

[Padam]

Hi ,

See your file now.

Narayan

Thanks Naryan for your reply in this regard, can you explain me how this formula is working????

 

[NARAYANK991]

Hi ,

Go through this link for an introduction :

http://chandoo.org/wp/2011/11/18/formula-forensics-003/

Narayan

 

[Padam]

Hi ,

Go through this link for an introduction :

http://chandoo.org/wp/2011/11/18/formula-forensics-003/

Narayan

Dear Naryan,

As per your guidance i have applied that formula in my sheet, where as now the data resides in two sheets then the earlier sample that is data which i want to look for is in summary sheet and from there i want data to be displayed in sheet 1 but i am getting the wrong result, please check in attached sample i have highlighted the issues in red color. Please guide me further, your help in this regard will be highly appreciable...

 

[NARAYANK991]

Hi ,

See your file now ; the construct used is the correct one ; earlier I had not used this because the starting column was column A , which is numerically 1.

If your starting column is different , then you need to use this construct.

COLUMN('Summary '!$C$10:$AG$10) - MIN(COLUMN('Summary '!$C$10:$AG$10)) + 1

If your starting column had been A , then you could have used :

COLUMN('Summary '!$A$10:$AG$10)

without the additional part highlighted in the first construct.

Narayan