Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup for part of a word
I have three columns of information.
column A - 6 digit number column B - 8 digit number and dash that correspond to column A column C - 6 digit number with other data that match one of the entries from column A I would like column D to give the result from column B that corresponds to column A based on the data from column C and if it matches (partially) column A. example A B C D 050505 04-121212 050505 - CO.fsa (formula here) 050452 05-214176 050505 - CO.fsa 052121 03-217496 050505 - CO.fsa In this example, I would like column D result to be 04-121212 for all three entries because that it the number listed in column C. In this instance, I would like column D to return 04-121212 for all three answers. So, I want the results from column C that go with Colum |
#2
|
|||
|
|||
Hi, maybe something like this (assuming your data starts in D2) ...
=Vlookup(Left(C2,Find(" ",C1,1)-1),A:B,2,0) If your values in column A are numerical, you'll need to coerce your lookup values ... =Vlookup(0+Left(C2,Find(" ",C1,1)-1),A:B,2,0) -- Regards, Zack Barresse, aka firefytr "jenhow" wrote in message ... I have three columns of information. column A - 6 digit number column B - 8 digit number and dash that correspond to column A column C - 6 digit number with other data that match one of the entries from column A I would like column D to give the result from column B that corresponds to column A based on the data from column C and if it matches (partially) column A. example A B C D 050505 04-121212 050505 - CO.fsa (formula here) 050452 05-214176 050505 - CO.fsa 052121 03-217496 050505 - CO.fsa In this example, I would like column D result to be 04-121212 for all three entries because that it the number listed in column C. In this instance, I would like column D to return 04-121212 for all three answers. So, I want the results from column C that go with Colum |
#3
|
|||
|
|||
jenhow wrote:
I have three columns of information. column A - 6 digit number column B - 8 digit number and dash that correspond to column A column C - 6 digit number with other data that match one of the entries from column A I would like column D to give the result from column B that corresponds to column A based on the data from column C and if it matches (partially) column A. example A B C D 050505 04-121212 050505 - CO.fsa (formula here) 050452 05-214176 050505 - CO.fsa 052121 03-217496 050505 - CO.fsa In this example, I would like column D result to be 04-121212 for all three entries because that it the number listed in column C. In this instance, I would like column D to return 04-121212 for all three answers. So, I want the results from column C that go with Colum If your data is in a range named "Tbl1", the following formula can be in a cell in Column D and filled down: =VLOOKUP(--LEFT(CELL("contents",INDEX(Tbl1,ROW(A3),3)),5),Tbl 1,2,0) Alan Beban |
#4
|
|||
|
|||
Thanks. That worked perfectly. But can you explain how the formula works to me?
VLOOKUP(LEFT(C1,FIND(" ",C2,1)-1),A:B,2,0) |
#5
|
|||
|
|||
The FIND function ..
FIND(" ",C2,1) ...gives us the location of the first space in thecell. We minus one to take out the character of the space (location). With the LEFT function that leaves us with the first part of the cell up to the space (just prior to). So it will reduce this .. 050505 - 121212 - 555aaa ... to this ... 050505 Then this value is plugged into the VLOOKUP function as a standard cell reference would be. The zero on the end is equivelant to the FALSE syntax statement of the formula. -- Regards, Zack Barresse, aka firefytr "jenhow" wrote in message ... Thanks. That worked perfectly. But can you explain how the formula works to me? VLOOKUP(LEFT(C1,FIND(" ",C2,1)-1),A:B,2,0) |
#6
|
|||
|
|||
Alan Beban wrote...
jenhow wrote: .... ___A__ ____B____ ____C__________ ____D____ 050505 04-121212 050505 - CO.fsa (formula here) 050452 05-214176 050505 - CO.fsa 052121 03-217496 050505 - CO.fsa In this example, I would like column D result to be 04-121212 for all three entries because that it the number listed in column C. .... If your data is in a range named "Tbl1", the following formula can be in a cell in Column D and filled down: =VLOOKUP(--LEFT(CELL("contents",INDEX(Tbl1,ROW(A3),3)),5),Tbl 1,2,0) Your use of -- in the 1st argument raises an issue that needs further discussion. If col A contains numbers formatted with leading zeros, you would need to convert the col C substrings to numbers. However, you're only pulling the first *5* characters from col C. That means you'd be looking up --("05050") == 5050 in the first row against the value 50505 in col A. No match. Your second argument to LEFT should be 6, not 5. On the other hand, if col A contains text that just happens to consist only of decimal digits, then converting the first argument to VLOOKUP to a number will again result in no match. In that case you want to omit the --. Since the OP's first column shows leading digits, there's some ambiguity about the actual data type. In the presence of such ambiguity, the robust approach requires INDEX and MATCH rather than VLOOKUP. If the OP's table were in A1:C3, the D1 formula would be D1: =INDEX($B$1:$B$3,MATCH(LEFT(C1,6),TEXT(--A1:A3,"000000"),0)) However, if the OP knows that col A is entirely numbers formatted with leading zeros or entirely text, then VLOOKUP would be better. D1: All numbers: =VLOOKUP(--LEFT(C1,6),$A$1:$B$3,2,0) All text: =VLOOKUP(LEFT(C1,6),$A$1:$B$3,2,0) Now let's discuss outright bugs. Since you tell the OP your formula should be "filled down", the implication is that the formula you show would correspond to the topmost cell in the 3rd col of Tbl1. However, your INDEX call would return the 3rd row from Tbl1 since ROW(A3) returns 3 rather than 1. Finally, there's the bizarre use of CELL("contents",INDEX(.)) rather than simply INDEX(.), which in turn should be replaced by a simple cell reference. Why the CELL call? Why the INDEX call? So, Alan, you don't always test the formulas you post either, eh? |
#7
|
|||
|
|||
Harlan Grove wrote:
Alan Beban wrote... jenhow wrote: ... ___A__ ____B____ ____C__________ ____D____ 050505 04-121212 050505 - CO.fsa (formula here) 050452 05-214176 050505 - CO.fsa 052121 03-217496 050505 - CO.fsa In this example, I would like column D result to be 04-121212 for all three entries because that it the number listed in column C. ... If your data is in a range named "Tbl1", the following formula can be in a cell in Column D and filled down: =VLOOKUP(--LEFT(CELL("contents",INDEX(Tbl1,ROW(A3),3)),5),Tbl 1,2,0) . . . Now let's discuss outright bugs. Since you tell the OP your formula should be "filled down", the implication is that the formula you show would correspond to the topmost cell in the 3rd col of Tbl1. However, your INDEX call would return the 3rd row from Tbl1 since ROW(A3) returns 3 rather than 1. . . . So, Alan, you don't always test the formulas you post either, eh? Not a failure to test. I tested on a 3-row range and simply mistakenly copied the formula from the 3rd row instead of from the first row when pasting into my posting. Thanks for pointing it out. Alan Beban |
#8
|
|||
|
|||
Alan Beban wrote...
.... OP's original table ___A__ ____B____ ____C__________ ____D____ 050505 04-121212 050505 - CO.fsa (formula here) 050452 05-214176 050505 - CO.fsa 052121 03-217496 050505 - CO.fsa AB's original formula =VLOOKUP(--LEFT(CELL("contents",INDEX(Tbl1,ROW(A3),3)),5),Tbl 1,2,0) Not a failure to test. I tested on a 3-row range and simply mistakenly copied the formula from the 3rd row instead of from the first row when pasting into my posting. Thanks for pointing it out. More BS from you. If col A row 1 were a number formatted as "000000", then your 1st arg to LOOKUP, no matter which col C cell you used, would be 5050, *NOT* 50505, so your VLOOKUP formula would have returned #N/A. If col A row 1 were text, then even if you had used the first 6 chars from any of the col C cells, you'd have converted the first VLOOKUP argument to a number which couldn't match *ANY* text values in col A, so again your VLOOKUP formula would have returned #N/A. Didn't test using the OP's data either, did you? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel Export Data to Word Format? | Excel Discussion (Misc queries) | |||
How to embed Word document into Excel and retain sizing, formatti. | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Embedded word doc changed to image-need to change back to word. | Excel Discussion (Misc queries) | |||
Vlookup of parts of a word in a range of Data | Excel Discussion (Misc queries) |