A different look at VLOOKUP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bigfoot17 - 13 Mar 2007 16:22 GMT

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.

Reply to this Message

bj - 13 Mar 2007 16:49 GMT

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

> I have reviewed every Vlookup post and I don't think I have seen this type of
> question.
[quoted text clipped - 12 lines]
> Any assistance is appreciated. Ususally it takes me longer to explain the
> problem than for someone to assist.

Reply to this Message

Don Guillett - 13 Mar 2007 17:08 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> 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))+
[quoted text clipped - 26 lines]
>> the
>> problem than for someone to assist.

Reply to this Message

bj - 13 Mar 2007 17:40 GMT

good idea

> IF?? you were to do it this way then it is suggested that you name each
> table T1, T2
[quoted text clipped - 30 lines]
> >> the
> >> problem than for someone to assist.

Reply to this Message

Bigfoot17 - 14 Mar 2007 19:21 GMT

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.

Reply to this Message

Don Guillett - 14 Mar 2007 19:59 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> 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
[quoted text clipped - 24 lines]
> You guys have been helpful, but if you could help me this extra miel it is
> appreciated.

Reply to this Message

Bigfoot17 - 14 Mar 2007 20:44 GMT

==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
[quoted text clipped - 47 lines]
> > You guys have been helpful, but if you could help me this extra miel it is
> > appreciated.

Reply to this Message

Don Guillett - 14 Mar 2007 22:06 GMT

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.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> ==Did you even try to modify my original post? - ABSOLUTELY! I appreciate
> all
[quoted text clipped - 66 lines]
>> > is
>> > appreciated.

Reply to this Message

Don Guillett - 14 Mar 2007 23:27 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> The dots have to do with the WITH statement and identify the .range and
> the .cells in the source workbook.
[quoted text clipped - 75 lines]
>>> > it is
>>> > appreciated.

Reply to this Message

Don Guillett - 13 Mar 2007 16:58 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I have reviewed every Vlookup post and I don't think I have seen this type
>of
[quoted text clipped - 15 lines]
> Any assistance is appreciated. Ususally it takes me longer to explain the
> problem than for someone to assist.

Reply to this Message

Martin Fishlock - 13 Mar 2007 17:16 GMT

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.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Reply to this Message

T. Valko - 13 Mar 2007 22:57 GMT

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

Reply to this Message

T. Valko - 13 Mar 2007 23:11 GMT

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

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

Biff

> Here's a possibility:
>
[quoted text clipped - 40 lines]
>> the
>> problem than for someone to assist.

Reply to this Message