LOOKUP 2 values in array

[digicat]

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.

 

[Guest]

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

 

[Frank Kabel]

Hi
see your post in Excel.worksheet.functions

 

[Guest]

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