Intersect function to find matching row AND column?

[Skywalker]

Hello,

I have column headings in row 1 of a worksheet and dates in column A (starting from row 2 onwards).

James Tom Alice Craig
21/04/2006
21/05/2006
22/06/2006

I would like to find the cell where the column and row match to pre-determined values: So for example, say I want to find the the cell where column heading in row 1 equals Orange and the date in column A equals 22-dec-2008.

So if orange is in column 6 (F1) and 22-dec-2008 occures in row 74 (A74), then I cell I'm looking for is F74.

Is there a way to find the cell using vba. I've seen some references to the intersect function but am having trouble using it. Hope someone can shed some light.

Some Notes:
1) Column headers from B1 to Q1
2) Dates are from A2 to A1647
3)All column and row values are unique

Thanks,

Skywalker

[Bob Phillips]

Without VBA

=INDEX(A1:Z100,MATCH(--"2008-12-22",A1:A100,0),MATCH("orange",A1:Z1,0))

[Skywalker]

Thanks XLD for the solution. I have seen references to use of index and match function before to do this kind of stuff but Ideally I'm looking for a vba solution as I need to add it to existing vba code where this requirement would only form a only small part of the total VBA procedure. If it cannot be done any other way then I will use the set up you suggest and have a sheet in the workbook that performs what I need to get the cell reference and refer to it directly in the vba code.

Thanks,

SW

Excellor

[Bob Phillips]

Public Function fxIntersect(LookupHead As String, LookupDate As Date) As Variant
Dim RowNum As Long
Dim ColNum As Long

ColNum = Rows(1).Find(LookupHead, after:=Range("A1")).Column
RowNum = Columns(1).Find(LookupDate, after:=Range("A1")).Row
If RowNum > 0 And ColNum > 0 Then

fxIntersect = Cells(RowNum, ColNum).Value
End If
End Function


use like



MsgBox fxintersect("orange",dateserial(2008,12,22))