Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Hopefully, I can explain without too much complexity!
The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Oops! I think I forgot something important. VLOOKUP will lookup the data in
L1436 in the named range called DLVYTIME (first column) and enter the first digits/numbers up to the space in SALESTYPE in N1436. If this is too confusing, ask me to repost. I need this formula, so I will gladly try to clarify. Thank you. Connie Martin. "Connie Martin" wrote: Hopefully, I can explain without too much complexity! The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Are you saying you just need the data up to the first space, or a lookup. In
your example do you just want it to return B650TBG on the first line? -- -John Please rate when your question is answered to help us and others know what is helpful. "Connie Martin" wrote: Oops! I think I forgot something important. VLOOKUP will lookup the data in L1436 in the named range called DLVYTIME (first column) and enter the first digits/numbers up to the space in SALESTYPE in N1436. If this is too confusing, ask me to repost. I need this formula, so I will gladly try to clarify. Thank you. Connie Martin. "Connie Martin" wrote: Hopefully, I can explain without too much complexity! The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
I don;t know about your source data. It also seems that your named
ranges are not particularly useful here. Maybe this formula? =LEFT(VLOOKUP(L1436,Sheet1!a1:e2000,3,0),FIND(" ",VLOOKUP(L1436,Sheet1!a1:e2000,3,0))-1) Does this help? Kostis Vezerides Connie Martin wrote: Oops! I think I forgot something important. VLOOKUP will lookup the data in L1436 in the named range called DLVYTIME (first column) and enter the first digits/numbers up to the space in SALESTYPE in N1436. If this is too confusing, ask me to repost. I need this formula, so I will gladly try to clarify. Thank you. Connie Martin. "Connie Martin" wrote: Hopefully, I can explain without too much complexity! The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Yes, to your second question. Let me see if I can do a better job with my
question. In cell N1436 I need a formula that will look at the data in L1436 and find the same data in the first column of the named range DLVYTIME, and then look at the named range SALESTYPE and put in N1436 the letter-number combination up to the first space, which is this case would be B650TBG. Should I have only one named range? Would that be simpler? I think so. In that case, the current SALESTYPE range would be the 3rd column of DLVYTIME named range. Here is a sample of what it looks like: COL. A COL. B COL. C 100008056 7 CD667 W 6.375 X6.375 X1.750 100008057 42 CD668 W 6.370 X6.370 X2.000 100008072 42 CD6610 W 6.370 X6.370 X2.500 100008073 42 C91 W 8.875 X6.750 X1.625 100009421 28 B650TBG CB 300X450 STD SET 100009422 28 B650TBGWCB 180X400 STD SET So, in cell L1436 on the other worksheet, I would have, for example, 100009421. Therefore, in cell N1436 the formula would return B650TBG. "John Bundy" wrote: Are you saying you just need the data up to the first space, or a lookup. In your example do you just want it to return B650TBG on the first line? -- -John Please rate when your question is answered to help us and others know what is helpful. "Connie Martin" wrote: Oops! I think I forgot something important. VLOOKUP will lookup the data in L1436 in the named range called DLVYTIME (first column) and enter the first digits/numbers up to the space in SALESTYPE in N1436. If this is too confusing, ask me to repost. I need this formula, so I will gladly try to clarify. Thank you. Connie Martin. "Connie Martin" wrote: Hopefully, I can explain without too much complexity! The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Sorry. This doesn't work, and I don't undestand the formula well enough to
try to adapt it. This formula gives me #N/A and a pop-up box "File not Found". Connie "vezerid" wrote: I don;t know about your source data. It also seems that your named ranges are not particularly useful here. Maybe this formula? =LEFT(VLOOKUP(L1436,Sheet1!a1:e2000,3,0),FIND(" ",VLOOKUP(L1436,Sheet1!a1:e2000,3,0))-1) Does this help? Kostis Vezerides Connie Martin wrote: Oops! I think I forgot something important. VLOOKUP will lookup the data in L1436 in the named range called DLVYTIME (first column) and enter the first digits/numbers up to the space in SALESTYPE in N1436. If this is too confusing, ask me to repost. I need this formula, so I will gladly try to clarify. Thank you. Connie Martin. "Connie Martin" wrote: Hopefully, I can explain without too much complexity! The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Okay, this is to both John and Kostis. I used the "LEFT" part of your
formula, Kostis, and I deleted the name SALESTYPE and redefined the named range DLVYTIME to include all columns, and then I put this formula in N1436, which partly works: =LEFT(VLOOKUP($L1436,DLVYTIME,3,FALSE)) What it's doing is putting only the first letter in, which is "B". How do I get it to return everything up to the first space, which would be B650TBG? This is getting exciting.....we're on to it, just not quite all there! Connie Martin "Connie Martin" wrote: Hopefully, I can explain without too much complexity! The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
You can change the range as needed but here is what I did, i named the 3
columns you gave in your example Salestype and entered this formula: =LEFT(VLOOKUP(L1436,Salestype,3,0),FIND(" ",VLOOKUP(L1436,Salestype,3,0))-1) This used 100009421 as the test and returned B650TBG you can change salestype to whatever you want but the first column must contain the number i.e. 100009421. That being column 1 of your range count over to the one with your item number, if it is not column 3 then replave the two 3's in the formula with the correct column number. -- -John Please rate when your question is answered to help us and others know what is helpful. "Connie Martin" wrote: Okay, this is to both John and Kostis. I used the "LEFT" part of your formula, Kostis, and I deleted the name SALESTYPE and redefined the named range DLVYTIME to include all columns, and then I put this formula in N1436, which partly works: =LEFT(VLOOKUP($L1436,DLVYTIME,3,FALSE)) What it's doing is putting only the first letter in, which is "B". How do I get it to return everything up to the first space, which would be B650TBG? This is getting exciting.....we're on to it, just not quite all there! Connie Martin "Connie Martin" wrote: Hopefully, I can explain without too much complexity! The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Thank you, John!! That works superbly! Have a great day!! Connie
"John Bundy" wrote: You can change the range as needed but here is what I did, i named the 3 columns you gave in your example Salestype and entered this formula: =LEFT(VLOOKUP(L1436,Salestype,3,0),FIND(" ",VLOOKUP(L1436,Salestype,3,0))-1) This used 100009421 as the test and returned B650TBG you can change salestype to whatever you want but the first column must contain the number i.e. 100009421. That being column 1 of your range count over to the one with your item number, if it is not column 3 then replave the two 3's in the formula with the correct column number. -- -John Please rate when your question is answered to help us and others know what is helpful. "Connie Martin" wrote: Okay, this is to both John and Kostis. I used the "LEFT" part of your formula, Kostis, and I deleted the name SALESTYPE and redefined the named range DLVYTIME to include all columns, and then I put this formula in N1436, which partly works: =LEFT(VLOOKUP($L1436,DLVYTIME,3,FALSE)) What it's doing is putting only the first letter in, which is "B". How do I get it to return everything up to the first space, which would be B650TBG? This is getting exciting.....we're on to it, just not quite all there! Connie Martin "Connie Martin" wrote: Hopefully, I can explain without too much complexity! The worksheet that has the columns of data that I want to look up has 4 columns of data. In the 3rd column the data varies widely, but here's a general example: B650TBG CB 325X400 STD SET In another worksheet (of the same workbook), I need a formula, which is going to start in cell N1436, that will go look up the data in this 3rd column, which I have named SALESTYPE, and will put in the cell N1436 only the combination of letters and numbers up to the first space---in this case B650TBG. This is not consistent, however. The first space could be after just three digits, as in this example: C91 W 8.875 X6.750 X1.625, in which case it would only put C91 in cell N1436. Hope this can be done. Thank you. Connie Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |