|
A different look at VLOOKUP
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$5 7,6,0)),VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),VLOOKU P(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. |
A different look at VLOOKUP
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,VLOOKU P(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 "Bigfoot17" wrote: 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$5 7,6,0)),VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),VLOOKU P(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. |
A different look at VLOOKUP
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 -- Don Guillett SalesAid Software "bj" wrote in message ... 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,VLOOKU P(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 "Bigfoot17" wrote: 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$5 7,6,0)),VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),VLOOKU P(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. |
A different look at VLOOKUP
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. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Bigfoot17" wrote: 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$5 7,6,0)),VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),VLOOKU P(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. |
A different look at VLOOKUP
good idea
"Don Guillett" wrote: 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 -- Don Guillett SalesAid Software "bj" wrote in message ... 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,VLOOKU P(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 "Bigfoot17" wrote: 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$5 7,6,0)),VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),VLOOKU P(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. |
A different look at VLOOKUP
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: InsertNameDefine 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 "Bigfoot17" wrote in message ... 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$5 7,6,0)),VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),VLOOKU P(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. |
A different look at VLOOKUP
A slight tweak (still needs to be array entered):
=VLOOKUP(A1,INDIRECT(INDEX(tables,MATCH(1,COUNTIF( INDIRECT(tables),A1),0))),6,0) Biff "T. Valko" wrote in message ... 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: InsertNameDefine 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 "Bigfoot17" wrote in message ... 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$5 7,6,0)),VLOOKUP(B2,'Sheet1'!$K$8:$P$57,6,0),VLOOKU P(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. |
A different look at VLOOKUP
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. |
A different look at VLOOKUP
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. |
A different look at VLOOKUP
==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. |
A different look at VLOOKUP
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. |
A different look at VLOOKUP
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. |
| All times are GMT +1. The time now is 04:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com