Anytime you use a function you need to use application
ie application.match
application.index
I don't think you will need to use application.worksheetfunction.
application alone should do it.

Without looking at this in depth, you could probably avoid using the
functions if you used
vba FIND and OFFSET

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
Sorry accidently hit the wrong button :-/

The problem I'm now having is that SrcChk2 (in the WorksheetFunction) is
generating a compile error "Sub or Function not defined". I guess I'm
not
as smart I think because I can't seem to figure it out!

"mtsark" wrote:

Thanks Don,
Not having much experiance with using INDEX and MAtch required a little
more
research on my part but I managed to develop the following code:

Dim ProjAry As Range, SrcChk1 As Range, DestChk1 As Range
Dim SrcChk2a As Range, DestChk2 As Range

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")
Set SrcChk1 = Sheets("Raw Projections").Range("B6:B273")
Set SrcChk2a = Sheets("Raw Projections").Range("D6:D273")
Sheets("Projections").Range("G2").Select

Do
Set DestChk1 = ActiveCell.Offset(0, -5)
Set DestChk2 = ActiveCell.Offset(0, -2)
ActiveCell.Value = Application.WorksheetFunction.Index(ProjAry,
_
Match(1, (SrcChk1 = DestChk1) * (SrcChk2a = DestChk2), 0), 6)
ElseIf IsError(ActiveCell.Value) Then
ActiveCell.Value = "0"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))


"Don Guillett" wrote:

To do with a formula use match "PHX" to find the row and combine with
index
for the value

--
Don Guillett
SalesAid Software

"mtsark" wrote in message
...
I have written a macro to combine mutiple (3+) worksheets into one.
I have used VLOOKUP to retrieve the majority of the required data.
The problem I am running into is when there are multiple entries for
the
same person.
Now there are data entry issues that if resolved could eleviate the
problem
but I need to deal with the situation at hand. :(

An Example:

Worksheet A (Destination Data):
Column A Coulmn B Column C
Josh Smith PHX
Jim Smith DFW
John Smith ORL

Worksheet B (Source Data):
Column A Coulmn B Column C
J Smith PHX 20
J Smith DFW 120
J Smith ORL 2

Now the location in Coulmn B does not change with respect to the
name in
Column A (ie. Josh Smith is always at PHX). What I was hoping to do
was
have
VLOOKUP compare the values in Column B for each entry in Column A
and
transfer the value in Column C to Worksheet A. Is this possible?

Thanks,
Matt