It'd probably be a good idea to include a test to make sure the lookup value
is actually present otherwise you'll get a "false positive" (read: incorrect
result).

Array entered:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(B12,A2:E10))))*(B12<""),INDEX(A1 :E1,MAX((ISNUMBER(SEARCH(B12,A2:E10)))*COLUMN(A2:E 10)-MIN(COLUMN(A2:E10))+1)),"not
found")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Try this array formula** :

Row 1 A1:E1 = Clerk headers

Data in the range A2:E10

B12 = lookup value

=INDEX(A1:E1,MAX((ISNUMBER(SEARCH(B12,A2:E10)))*CO LUMN(A2:E10)-MIN(COLUMN(A2:E10))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"mmanis" wrote in message
...
I need help with a lookup function. I have an excel spreedsheet with 5
columns. , Clerk1, Clerk2....clerk5
The clerk fields contain text & numeric values (assignment codes). This
is
a general field where any text can be inputted with the numeric value. I
need a formula to search all the columns, find a particular value (ie
"15.06") and identify which clerk is working on that assignment. In this
case, I would need the formula's value to equal "Clerk2" if I was looking
up
"15.06". The columns are not sorted & contain a variety/combination of
text
and numbers in each cell. All the numbers will have the decimal followed
by
two digits.

Col A Col B

Row1 Clerk1 Clerk2
Row2 12.07 for JS 63.07, 15.06 due TR
Row3 899.07 & 701.07 for RO 229.07-231.07

row50 23.07 TR for RO 19.07 & 11.07