Sub findcol() 'Another way to do this by finding the address and using
offset.

With Workbooks("sourcesheet.xls").Sheets("sheet7")
x = Range("b2")
On Error Resume Next
For i = 2 To 74 Step 9
mc = .Range(.Cells(8, i), .Cells(100, i)).Find(x).Address
'MsgBox mc
If mc 0 Then Exit For
Next i
Range("b3").Value = .Range(mc).Offset(, 5)
'instead of the next 2 lines
'Range("b3").Value = Application. _
'VLookup(x, .Range(.Cells(8, mc), .Cells(100, mc + 5)), 5)
End With
End Sub

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
The dots have to do with the WITH statement and identify the .range and
the .cells in the source workbook.
The macro simply looks through col 2 (b) to see if there is a match. If
not, it looks 9 columns to the right until it finds a match and quits.
Then a regular vlookup is done using that column and the next 5 columns
that make up that particular table. You did NOT say if it is now working
for you. Send a source workbook to me if desired.

--
Don Guillett
SalesAid Software

"Bigfoot17" wrote in message
...
==Did you even try to modify my original post? - ABSOLUTELY! I appreciate
all
input. I did run into some problems with it and tried to work it out
myself,
but did not understand a couple of the statements so it was difficult to
modify. I only found four dots so that may be the problem.

==Had you created the names in the destination workbook - Arrgghhh! It
never crossed my mind

==Thanks for the re-write I will work with that as well.

"Don Guillett" wrote:

Did you even try to modify my original post. This has just been tested
from
a new workbook with the source workbook open but the new workbook as the
active workbook. Notice the dot placement ( . ) in 6 places.
NO named ranges necessary. Looks in each column until it finds the
value.
Tested with numbers but should be just fine with text. Just watch for
spelling,capitalization, spaces, etc.

BTW. Had you created the names in the destination workbook referring to
the
source workbook you would not have had the problem with the path.

Sub findcol() ' From Destination workbook
With Workbooks("sourceworkbook.xls").Sheets("Sheet7")
x = Range("b2")' what you are looking for
On Error Resume Next
For i = 2 To 74 Step 9 ' 74 columns
mc = .Range(.Cells(8, i), .Cells(100, i)).Find(x).Column
If mc 0 Then Exit For
Next i
Range("b3").Value = Application. _
VLookup(x, .Range(.Cells(8, mc), .Cells(100, mc + 5)), 6)
End With
End Sub

--
Don Guillett
SalesAid Software

"Bigfoot17" wrote in message
...
My head has been spinning all day to the point I have not taken a
break to
say THANKS for the input. I have not been successful yet but that is
because
I left out one piece of valuable information: The VLOOOKUP is to a
seperate
Workbook/file!

To summarize:
[1] The lookup data only appears once in the various tables
[2] The value being returned is text (not a number)
[3] I was successful in defining names, but note that the formula was
quite
long with consecutive IFS. But when the file was saved the path to
the
second file is saved in the formula making it go over the limitations
which
truncated the formula.
[4] I have been working on a VBA macro which I think would be better
but I
am having problems referencing the filename AND defined-named range in
the
vlookup.

i.e., This works...
Range("D" & i).Value = Application.VLookup(x,
Workbooks("file2.xls").Worksheets("1").Range("B8:G 57"), 6, 0)
...BUT I want to be able to use the named tables so I can have it loop
through looking at defined-name range T1 then T2 then T3 etc.

You guys have been helpful, but if you could help me this extra miel
it is
appreciated.