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