OK Beau I have put some work in on this. Have not tested to the nth degree
but it should point you in the right direction. I have done this as an
exercise because I cant resist a challenge but I still think that Dave
Petersons comments are extremely valid.

A couple of points that I am not sure if you are aware of. A space and
underscore at the end of a line is a line break in an otherwise single line
of code.

A line break cannot be inserted in the middle of strings enclosed in double
quotes. However, the double quotes can be closed off and then an ampersand
(&) to concatenate followed by the line break and then on the next line the
double quotes are opened again. I used this method in all the InputBox code
but in the last line of code I arranged the line breaks outside of the double
quoted strings where ampersands already existed. (I like to use line breaks
so that the code can be posted in limited space and the code can be copied
and used as is without editing all the lines that are broken by posting.)

The following lines of code return the workbook name as well as sheet name
and cell address. However, when these are inserted in the formula bar in
excel, excel automatically deletes the workbook and sheet name parameters if
not required.

strDataCell = rngDataCell.Address(0, 0, , True)
strIndexArray = rngIndexArray.Address(1, 1, , True)
strIndexRange = rngIndexRange.Address(1, 1, , True)

The 0,0 returns address as A1 style and 1,1 as $A$1 (absolute). They can
also be mixed to return $A1 or A$1.

Anyway have fun with it and let me know how it goes.

Sub TestIndexMatch()

Dim rngDataCell As Range
Dim rngIndexArray As Range
Dim rngIndexRange As Range
Dim rngMatchRange As Range

Dim strDataCell As String
Dim strIndexArray As String
Dim strIndexRange As String

Dim lngMatchRangeCol As Long
Dim lngColToInsert As Long

Dim lngIndexArrayColMin As Long

Set rngDataCell = Application.InputBox _
(Prompt:="Select CELL in destination spreadsheet " & _
"containing info to be matched...", Type:=8)

Set rngIndexArray = Application.InputBox _
(Prompt:="Highlight ARRAY in source spreadsheet to " & _
"include matching data and data to be copied...", Type:=8)

Set rngIndexRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where " & _
"matching data is held...", Type:=8)

Set rngMatchRange = Application.InputBox _
(Prompt:="Highlight COLUMN in source spreadsheet where " & _
"data to be copied is held...", Type:=8)

strDataCell = rngDataCell.Address(0, 0, , True)
strIndexArray = rngIndexArray.Address(1, 1, , True)
strIndexRange = rngIndexRange.Address(1, 1, , True)

lngIndexArrayColMin = rngIndexArray.Cells(1, 1).Column

lngMatchRangeCol = rngMatchRange.Column

lngColToInsert = lngMatchRangeCol - lngIndexArrayColMin + 1

ActiveCell.Formula = "=INDEX(" & strIndexArray & ",MATCH(" & _
strDataCell & "," & strIndexRange & ",0)," & _
lngColToInsert & ")"

End Sub

--
Regards,

OssieMac