Index Match with multiple columns using input boxes
VBE -> Menu Bar -> Tools -> Options

I recommend turning off [ ] Auto Syntax Check and turning on [x] Require Variable Declaration



Turning off Auto Syntax Check will stop those annoying messages from popping up when you are writing code.  The VB Editor highlights syntax errors in red anyway.  The messages do nothing but break the workflow and slow you down.

Require Variable Declaration inserts Option Explicit at the top of newly created code modules.  Option Explicit requires that all variables be declared. Ideally, all variable should be declared and strongly typed.



Declaring Variables

These variables were not declared:

Dim LastSourceID As Long, LastTargetID As Long, LastValue As Long, MyColumn As Long, NextColumn As Long, TargetIDcolumn As Long, ValuesColumn As Long, SourceIDcolumn As Long


I prefer grouping my variables (at the top of the procedure), roughly 1 Dim statement per datatype. I do not like having to scroll to see what the code is doing.

The first thing that stands out is that the Title and Prompt variables are constant values and should be declared as Constants.

'set prompts
Const Prompt1 As String = "Select values to pull (1 or more columns)"
Const Prompt2 As String = "Select unique IDs on target sheet (1 column only)"
Const Prompt3 As String = "Select unique IDs on source sheet (1 column only)"
Const Prompt4 As String = "Select any range on target sheet"

'set titles
Const Title1 As String = "Source Sheet"
Const Title2 As String = "Target Sheet"
Const Title3 As String = "Source Sheet"
Const Title4 As String = "Target Sheet"


Constants can also be grouped but I usually don't because it can make them difficult to reference and modify.

'set prompts
Const Prompt1 As String = "Select values to pull (1 or more columns)",Prompt2 As String = "Select unique IDs on target sheet (1 column only)", Prompt3 As String = "Select unique IDs on source sheet (1 column only)",Prompt4 As String = "Select any range on target sheet"

'set titles
Const Title1 As String = "Source Sheet", Title2 As String = "Target Sheet", Title3 As String = "Source Sheet",Title4 As String = "Target Sheet"




Helper Variables

"Helper Variables" are great when they are helpful.  Many times they are just extra clutter that you have to work through.

Prompt1 is helpful because of the length of the string and these is something that you will probably consider modifying in the future.

Const Prompt1 As String = "Select values to pull (1 or more columns)"


Title1 is pretty short and easily modified where it is used.  I would not have bothered with it.

Set ValuesToPull = Application.InputBox(Prompt1, "Source Sheet", Type:=8)

I personally would save the space and use one of the following:

Set ValuesToPull = Application.InputBox("Select values to pull (1 or more columns)", "Source Sheet", Type:=8)


Set ValuesToPull = Application.InputBox( _
                   "Select values to pull (1 or more columns)", _
                   "Source Sheet" _
                   , Type:=8)

Set ValuesToPull = Application.InputBox( _
                   Prompt:="Select values to pull (1 or more columns)", _
                   Title:="Source Sheet" _
                   , Type:=8)


Are Source1, Target1, SourceIDcolumn, and LastSourceID really needed?  

I like to use the Optometrist Analogy when reviewing code.

Which is clearer, Code 1 or Code 2?

Code 1:

With Source1
    Set SourceIDs = .Range(.Cells(1, SourceIDcolumn), .Cells(LastSourceID, SourceIDcolumn))
End With


Code 2:

With SourceIDs.Worksheet
    Set SourceIDs = .Range(.Cells(1, SourceIDcolumn), .Cells(LastSourceID, SourceIDcolumn))
End With


Which is clearer, Code 2 or Code 3?

Code 3:

Set ValuesToPull = Intersect(SourceIDs.EntireRow, ValuesToPull.EntireColumn)


For me Code 3 is clearer.  But not everyone sees code the same way.  You should use the one that is easiest for you to read.  Note:  If you didn't choose Code 3, you might want to consider Lasik.



Repeated Code Patterns

Repeated code patterns in a subroutine is a sign that potions of the code can and probably should be extracted into a separate subroutine.  This will make both the main and extracted code easier to read, debug, and modify.

Public Function getInputRange(Prompt As String, Title As String, Optional AllowMultipleColumns As Boolean) As Range
    Dim Target As Range
    Set Target = Application.InputBox(Prompt, Title, Type:=8)

    If Target Is Nothing Then Exit Function
    If Target.Columns.Count &gt; 1 And Not AllowMultipleColumns Then Exit Function

    With Target.EntireColumn
        Set Target = Range(.Cells(1, 1), .Cells(.Rows.Count).End(xlUp))
    End With

    Set getInputRange = Target
End Function


Other Considerations

Are there Column Headers?
    hasColumnHeader = MsgBox("Do the Ranges Contain Column Headers?", vbYesNo, "Column Header")

Rows.Count should be qualified to the same worksheet the Target1.

Target1.Cells(Rows.Count




Performance

Turning off Application.Application.ScreenUpdating and setting Calculation = xlCalculationManual will greatly improve the performance of code that modify the formats and values of worksheets.

Public Sub SpeedBoost(TurnOn As Boolean)
    With Application
        .Calculation = IIf(TurnOn, xlCalculationManual, xlCalculationAutomatic)
        .ScreenUpdating = Not TurnOn
    End With
End Sub


Reading values from an array is a little faster than reading values from a range.  Writing multiple values from an array to a range in 1 operating is much faster than multiple writes.

Dictionaries are much faster than Application.Match. Reference:Analyst Cave: EXCEL VLOOKUP VS INDEX MATCH VS SQL VS VBA