Help with VLOOKUP

[mtsark]

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

 

[Don Guillett]

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

 

[sebastienm]

Hi Matt,
Not sure i understand completely but:

- what about inserting a new (temporary) column in front of column A and
setting it up with the formula:
= B1 & " - " & C1
(B is now the name, C the Location, C the values)
This will returns, in A: J Smith - PHX
- Then do a lookup on the new column A

I hope this helps,

Regards,
Sébastien

 

[mtsark]

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("D6273")
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))

 

[mtsark]

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!

 

[Don Guillett]

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

 

[mtsark]

Thanks again Don.
I will look into using FIND and Offset instead.

Matt

 

[Don Guillett]

let us know how it worked out

 

[mtsark]

Don, I think I finally got it. Just one more question. Even to a noob like
me the code I developed looks cumbersome. Is there a way to streamline it?

Dim ProjAry As Range, SrcChk1 As Range
Dim SrcFnd1 As String, SrcFnd2 As String, DestChk1 As String
Dim DestChk2 As String

Set ProjAry = Sheets("Raw Projections").Range("B6:O273")

Sheets("Projections").Range("G2").Select
Do
DestChk1 = ActiveCell.Offset(0, -5).Value
DestChk2 = ActiveCell.Offset(0, -2).Value
Set SrcChk1 = ProjAry.Find(What:=DestChk1, LookAt:=xlWhole, _
SearchOrder:=xlByColumns)

If Not SrcChk1 Is Nothing Then
SrcFnd1 = SrcChk1.Offset(0, 2).Value

If DestChk2 = SrcFnd1 Then
SrcFnd2 = SrcChk1.Offset(0, 5).Value
ActiveCell.Value = SrcFnd2
End If

Else
ActiveCell.Value = "0"
End If

If IsEmpty(ActiveCell) Then
ActiveCell.Value = "0"
End If

ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -6))

Raw Projections contains the data I need
Projections contains the two values (DestChk) I use to look up the value I
need (SrcFnd)
If both DestChk are true then the value is returned otherwise 0 is entered

Thanks again,
Matt