Macro to create Index/Match

[Beau Nidle]

I often have to match data from other workbooks and always have problems in
setting up Index/Match formulae.

Can anyone help me with a generic sort of macro that helps me set up the
formulae I need to bring in multiple (variable numbers) of columns of
matched data.

eg one time I might need to bring in the address details for the client in
column A into columns B to G where the details are in another
workbook/worksheet, or I might need to match sales figures for a reference
in column C into columns G to M.

In essence I need a macro that lets me just click on the column I want to
match, click on the column that has the matching data in, select the
columns with the return data, and click on the first column to paste into
and it creates the formula into the range for me.

Not sure if this is too complicated or I have just not explained myself
very well but any help would be appreciated.

 

[OssieMac]

Hi Beau,

What version of xl are you using? Index/Match used to be used back I think
about xl97. Vlookup is mostly used now but perhaps there is a specific need
for Index/Match. I have reservations about how much you can save by using
code.

Also, how competent are you with VBA. Do you actually need all the code or
just pointers in the right direction? I haven't actually tested this but I
think that Application.InputBox Method could be used to select a cell or
range to be used to build the formula. Would need to have several inputs to
get all the ranges.

 

[Dave Peterson]

I wouldn't use a macro. I'd learn to use those functions.

Debra Dalgleish's site may make that easier:
http://contextures.com/xlFunctions03.html

And if you're bringing back multiple columns of information from the same
worksheet, I'd use this technique. I think it will have less of an impact on
your calculation times.

Use a dedicated column to return the row number that has the match. Then use
the value in that cell as the index into the other columns.

For instance, say you want to look or an exact match for the value in A2 of the
current sheet with column D (just to be different) in Sheet2:

You could use this formula:
=match(a2,sheet2!d:d,0)
(say in B2)

This formula returns the number of the row in that range (column D of sheet2)
for the first match. It returns an error (#n/a) if there is no match.

Then if you wanted to bring back column F of that same matching row, you could
use this in another column:

=if(isna(b2),"No match found",index(sheet2!F:F,b2)

Since you're only looking for the match once (per row), the calculation time
should be quicker.

===========
ps. That row returned isn't always the row number of the other sheet.

If my =match() formula looked like:
=match(a2,sheet2!d78:d93,0)
and the value returned was 1, then the row that contained the match is 78.
(It's the first row of D7893.)

If the value returned was 7, what row had the match? And what cell contained
that match?
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
..
It was 7 rows into D7893, so that would be row 84 and the cell would be D84.

 

[Beau Nidle]

Hi Beau,

What version of xl are you using?

I'm using Excel 2003 mainly but would like it to be as generic as
possible.

Index/Match used to be used back I
think about xl97. Vlookup is mostly used now but perhaps there is a
specific need for Index/Match. I have reservations about how much you
can save by using code.

I am asking to use the Index/Match combination rather than Vlookup
because of the flexibility it gives in not needing the sources to be
sorted or lookup value needing to be in the first column.
Regarding time saved, at present if I want to bring in say 5 columns of
data I need to create the first Index/Match formula. Drag and copy
across 5 columns, edit each one (because of the absolute ranges in the
formula they do not update automatically) then copy down the columns. As
I often have to do this then it would be quite a time saver for me. It
could also be a useful tool for my colleagues who don't have the Excel
knowledge to be able to do it from scratch.

Also, how competent are you with VBA. Do you actually need all the
code or just pointers in the right direction? I haven't actually
tested this but I think that Application.InputBox Method could be used
to select a cell or range to be used to build the formula. Would need
to have several inputs to get all the ranges.

I was hoping someone out there would already have got one that works I
could use but some help in developing one would be appreciated.

I've got as far as being able to select the cells/ranges I need (Se
below) but I'm stuck in building the formula. I think it is because I
have ranges not strings (because I wanted to select rather than have to
type into an input box) but not sure.

Sub TestIndexMatch()

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

Set rngDataCell = Application.InputBox _
(Prompt:="Select CELL in destination spreadsheet containing info to be
matched...", 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)

ActiveCell.Formula = "=INDEX(" & rngMatchRange & ",MATCH(" & rngDataCell
& "," & rngIndexRange & ",0),1)"

Also I am not sure how to copy that formula across to bring in multiple
columns eg rngMatchRange being $B:$D not just a single column.

Any help would be appreciated
Beau Nidle

 

[OssieMac]

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 can’t resist a challenge but I still think that Dave
Peterson’s 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

 

[Beau Nidle]

Sorry for the delay in replying but I've not been well.
Many thanks for the work you've put in here for me. Nearly exactly what I
needed (but then I said it might be too complicated for a generic answer),
but close enough to give me the start I need to work the rest out for
myself.
The bits about returning an absolute/relative reference (0,0 or 1,1) as a
string are very useful to know.
Thanks very much again for your help. If I ever get it working exactly how
I want (not before as there are too many "experts" out there who will
criticise rather than help like you) I'll post it here for you and anyone
else to use. After all one of the great things about this group is that
there is always someone else out there who has tried to do the same thing
as you before. No point in reinventing the wheel as my old manager used to
say.
Cheers
Beau Nidle

 

[OssieMac]

No worries Beau. I look forward to hearing from you again in the future
because I always like to see what improvements can be made to anything I do.