CODEWITHSUNDEEP
excel
user3682157
user3682157

Reputation: 1695

Return column location of a value in Excel 2010

I have a simple ask: I am trying to return the column title for an inputted value. Example below

Column 1      Column 2       Column 3
  1             7               19
  3             31              33
  4             12              14

So if I inputted the value "31", I would like to return "Column 2"

Anyone know how this is done? Thanks!

Upvotes: 0

Views: 248

Answers (3)

barry houdini
barry houdini

Reputation: 46371

You can use this array formula

=INDEX(1:1,SMALL(IF(A2:C4=31,COLUMN(A2:C4)),1))

confirm with CTRL+SHIFT+ENTER

If 31 doesn't occur in A2:C4 you'll get an error [#NUM!]

If 31 occurs more than once in that range you'll get the header from the first occurence (i.e. the leftmost column)

Upvotes: 1

Fratyx
Fratyx

Reputation: 5797

Following your example, let us say headlines are in row 1 and the values in range A2:C4, the lookup value in D1, then this formula will do it: (for multiple occurences of the lookup value it will take the last fitting column)

=IFERROR(INDIRECT(ADDRESS(1,MAX((A2:C4=D1)*COLUMN(A2:C4)))),"None")

IMPORTANT: You have to make it an array formula. After typing or inserting this formula in the target cell or in the formula bar DON'T just hit 'Return' or leave the cell but hit CTRL+SHIFT+RETURN. If you did it right the formula will be surrounded by curly brackets in formula bar. ={IFERROR(INDIRECT(ADDRESS(1,MAX((A2:C4=D1)*COLUMN(A2:C4)))),"None")}. Otherwise you get wrong results.

For array formulas also look here.

Upvotes: 0

pnuts
pnuts

Reputation: 59485

Far from ideal but in say A10 and copied across: 

=IF(COUNTIF(A2:A9,31)=0,"",A1)

Upvotes: 0

Related Questions