A different look at VLOOKUP

[Guest]

I have reviewed every Vlookup post and I don't think I have seen this type of
question.

I have "groups of information" on Sheet 1:
Columns B-G, K-P, T-Y, AC-AH, AL-AQ, AU-AZ, BD-BZ, BM-BR, BV-CA

What I want to do is lookup on Sheet1 a Value in Column B and return the
value in G. But if the value is not in B look it up in K, if it isn't there
look it up in T, etc.

Here is what I have for looking the value up in the first two groups, but I
cannot get my arms around how to do this for 9-11 areas
IF($B2="","",IF(ISNA(VLOOKUP(B2,'Sheet1'!$B$8:$G$57,6,0)),VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),VLOOKUP(B2,'Sheet1'!$B$8:$G$57,6,0)))

Any assistance is appreciated. Ususally it takes me longer to explain the
problem than for someone to assist.

 

[Guest]

if the value in B2 will only be in one of the tables try
=if($B2="","",IF(ISNA(VLOOKUP(B2,'Sheet1'!$B$8:$G$57,6,0),0,VLOOKUP(B2,'Sheet1'!$B$8:$G$57,6,0))+IF(ISNA(VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0)),0,VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0))+ ...)
If it is text change the ,0, to ,"", and the + to &

if it can be in several groups and yo have a priority, you could use a
helper column with if(isna(vlookup() for each group and select just the first
one which shows

 

[Don Guillett]

How about something like this macro. I could be a function or automatic with
a worksheet_change event.

Sub findcol()
x = range("b2")
On Error Resume Next
For i = 2 To 74 Step 9
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 Sub

 

[Don Guillett]

IF?? you were to do it this way then it is suggested that you name each
table T1, T2
if(isna(vlookup(b2,t1,6,0)),etc t2

 

[Guest]

You need to do repeating vlookups using ifs.

as in:
=IF($B2="","",
IF(ISNA(VLOOKUP(B2,'Sheet1'!$B$8:$G$57,6,0))=false,
VLOOKUP(B2,'Sheet1'!$B$8:$G$57,6,0),
if(ISNA(VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0))=false,
VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),
if(ISNA(VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0))=false,
VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),....)))))

you will need to adjust the closing brackets.

 

[Guest]

good idea

IF?? you were to do it this way then it is suggested that you name each
table T1, T2
if(isna(vlookup(b2,t1,6,0)),etc t2

 

[T. Valko]

Here's a possibility:

Lot's of assuming here!

The lookup_value *is not repeated* anywhere else in *any* range.

Name your ranges like tbl, tbl2, tbl3, etc

Create this named formula:

Insert>Name>Define
Name: tables
Refers to: ={"tbl1","tbl2","tbl3"}
OK

Lookup value in A1

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=VLOOKUP(A1,INDIRECT(INDEX(tables,MATCH(1,COUNTIF(INDIRECT("tbl"&ROW(INDIRECT("1:3"))),A1),0))),6,0)

Biff

 

[T. Valko]

A slight tweak (still needs to be array entered):

=VLOOKUP(A1,INDIRECT(INDEX(tables,MATCH(1,COUNTIF(INDIRECT(tables),A1),0))),6,0)

Biff

 

[Guest]

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:G57"), 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.

 

[Don Guillett]

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

 

[Guest]

==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.

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
(e-mail address removed)

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:G57"), 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.

 

[Don Guillett]

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
(e-mail address removed)

==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.

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
(e-mail address removed)

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:G57"), 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.

 

[Don Guillett]

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
(e-mail address removed)

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
(e-mail address removed)

==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.

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
(e-mail address removed)
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:G57"), 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.