LOOKUP 2 values in array
 

The table has dates in the columns and names in the rows
An x indicates who's on duty that day.

.............01-10-2004 ... 02-10-2004 ... 03-10-2004 .....
Sonja .........x
Dirk ...............................x
Katia .................................................. .x
Jan .............x

When I type a particular date, Excel should show the name with the x

03-10-2004
KATIA

Who can help me solve the problem?
I have no knowledge of VBA code.


--
digicat
------------------------------------------------------------------------
digicat's Profile: http://www.excelforum.com/member.php...o&userid=14920
View this thread: http://www.excelforum.com/showthread...hreadid=273274

LOOKUP 2 values in array
 

In a public module:

Function strAssigned(rngTable As Range, dteDate As Date, strIdentifier As
Variant) As String
Dim intCol As Integer
Dim lngRow As Long
On Error Resume Next

intCol = rngTable.Rows(1).Find(What:=dteDate, LookAt:=xlWhole,
LookIn:=xlFormulas).Column
If intCol 0 Then
lngRow = rngTable.Columns(intCol).Find(What:=strIdentifier,
LookAt:=xlWhole, LookIn:=xlValues).Row
If lngRow 0 Then
strAssigned = rngTable.Cells(lngRow, 1).Value
Else
strAssigned = "Not assigned"
End If
Else
strAssigned = "Date not found"
End If
End Function

On the worksheet, enter the formula "=strAssigned(~the range of the
table~,~the cell containing the date you want to find~,~the letter (or
whatever) used to cross match~)

In cell B31, that might read: =strAssigned($A$1:$H$25,A30,"x")

Let me know if it works for you.

Dale Preuss


"digicat" wrote:


The table has dates in the columns and names in the rows
An x indicates who's on duty that day.

.............01-10-2004 ... 02-10-2004 ... 03-10-2004 .....
Sonja .........x
Dirk ...............................x
Katia .................................................. .x
Jan .............x

When I type a particular date, Excel should show the name with the x

03-10-2004
KATIA

Who can help me solve the problem?
I have no knowledge of VBA code.


--
digicat
------------------------------------------------------------------------
digicat's Profile: http://www.excelforum.com/member.php...o&userid=14920
View this thread: http://www.excelforum.com/showthread...hreadid=273274

LOOKUP 2 values in array
 

Hi
see your post in Excel.worksheet.functions

--
Regards
Frank Kabel
Frankfurt, Germany

"digicat" schrieb im Newsbeitrag
...

The table has dates in the columns and names in the rows
An x indicates who's on duty that day.

............01-10-2004 ... 02-10-2004 ... 03-10-2004 .....
Sonja .........x
Dirk ...............................x
Katia .................................................. .x
Jan .............x

When I type a particular date, Excel should show the name with the x

03-10-2004
KATIA

Who can help me solve the problem?
I have no knowledge of VBA code.


--
digicat
---------------------------------------------------------------------
---
digicat's Profile:
http://www.excelforum.com/member.php...o&userid=14920
View this thread:
http://www.excelforum.com/showthread...hreadid=273274

LOOKUP 2 values in array
 

I have sent you an email with an excel file attachment that should do what
you need !


"digicat" wrote:


The table has dates in the columns and names in the rows
An x indicates who's on duty that day.

.............01-10-2004 ... 02-10-2004 ... 03-10-2004 .....
Sonja .........x
Dirk ...............................x
Katia .................................................. .x
Jan .............x

When I type a particular date, Excel should show the name with the x

03-10-2004
KATIA

Who can help me solve the problem?
I have no knowledge of VBA code.


--
digicat
------------------------------------------------------------------------
digicat's Profile: http://www.excelforum.com/member.php...o&userid=14920
View this thread: http://www.excelforum.com/showthread...hreadid=273274