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

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

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

I was finally able to get back to the project at hand. Thank you for your
response. I set up the array formula, but seem to have one problem. If the
lookup value is 1.07, the formula is picking up other instances where that
value may fall. For example, Column B had the value 1.07, column D had the
value of 101.07. Column D became the the value of the array formula. I am
not sure how to have the exact value found when the lookup value maybe a
smaller part of an larger number and may appear in the column after the
larger number (24.07 not 427.07)

"T. Valko" wrote:

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