?B?T3NzaWVNYWM=?= wrote in
:

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