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

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

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

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

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

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

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

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

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