I have a chart with anywhere between 1 to 12 columns, and anywhere between 1
to 144 rows. The columns used are anywhere between K-V, and rows range from
5 to 148. Now, data is not in every cell in this range, and the number of
columns and rows is determined by another number used, so not even every
column is used. But the maximum potential number of rows, columns, and cells
are those.

What I want to do is reference the data that is in this range. Row 4
contains the headers and names of each column. For example:

------K----L----M----N---- (Column locations)
(4)___A___B___C___D___ (Column text name, not column location)
(5)___1___2___3___4___ (Data)
(6)___5___6___7___8___ (Data)

^-- Row Numbers

What I want to do, is given a data value, I want to return the Column Name
(row 4) that the data falls under.

For instance, if I'm searching for the data value of "7", I want to return
"C".

What's the easiest way to do this? Thanks!

By the way, for the return value in my example when searching "7" I want to
return "C" in the next column.

Assuming there is only one instance of the lookup value...

K2 = lookup value

Array entered** :

=INDEX(K4:V4,MAX((K5:V148=K2)*COLUMN(K5:V148))-MIN(COLUMN(K5:V148))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
I have a chart with anywhere between 1 to 12 columns, and anywhere between
1
to 144 rows. The columns used are anywhere between K-V, and rows range
from
5 to 148. Now, data is not in every cell in this range, and the number of
columns and rows is determined by another number used, so not even every
column is used. But the maximum potential number of rows, columns, and
cells
are those.

What I want to do is reference the data that is in this range. Row 4
contains the headers and names of each column. For example:

------K----L----M----N---- (Column locations)
(4)___A___B___C___D___ (Column text name, not column location)
(5)___1___2___3___4___ (Data)
(6)___5___6___7___8___ (Data)

^-- Row Numbers

What I want to do, is given a data value, I want to return the Column Name
(row 4) that the data falls under.

For instance, if I'm searching for the data value of "7", I want to return
"C".

What's the easiest way to do this? Thanks!