column and Row reference to value in table

[bamafamily]

Hey All,
I just found this forum and it looks great!!
I have had a problem for quite some time and can't seem to get it t
work..

Excel2003:
I have column headings labeled: Orange, green, red, blue in B1:E
respectively
I have Row headings labeled: car, boat, plane, train in A2:A
respectively
I have numerical data in B2:E5

I want to search/find the value in the range B2:E5 and return BOTH th
row and column heading

Example:
the value 222 is in cell E5
I want to search/find 222 and return the 2 values of "Blue" an
"Train"

Is this possible with just a function??
Does it have to match exactly or can it be "+-1"?(assuming intege
data)
If the value appears more than once, can I return more than 1 set o
values??

thx for all the help
Bam

 

[Myrna Larson]

All of these are array formulas, which you must enter with CTRL+SHIFT+ENTER.

For the row heading alone:

=IF(COUNTIF(B2:E5,222)=0,"",INDEX(A:A,MAX(IF(B2:E5=222,ROW(B2:E5),0))))

For the column heading alone:

=IF(COUNTIF(B2:E5,222)=0,"",INDEX(1:1,MAX(IF(B2:E5=222,COLUMN(B2:E5),0)))

Combining the two:

=IF(COUNTIF(B2:E5,222)=0,"",INDEX(1:1,MAX(IF(B2:E5=222,COLUMN(B2:E5),0)))&"
"&INDEX(A:A,MAX(IF(B2:E5=222,ROW(B2:E5),0))))

These will work correctly if there's just one occurrence. If you have 222 in
B3 and E5, the row number will be 5 and the column 2; cell B5 in fact doesn't
contain 222.

One could do this relatively easily with a VBA function, of course.

Here's the function. YOu must insert a module into your workbook, and paste
the code below into it. Then, if you put the number you're looking for in A7,
the formula is

=Description(A7,$A$1:$E$5)


Function Description(X As Long, Table As Range) As String
Dim v As Variant
Dim R As Long
Dim C As Long
Dim S As String

v = Table.Value
For R = 2 To UBound(v, 1)
For C = 2 To UBound(v, 2)
If v(R, C) = X Then
S = S & ", " & v(1, C) & " " & v(R, 1)
End If
Next C
Next R
Description = Mid$(S, 3)

End Function