Vlookup for part of a word

J

jenhow

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
 
Z

zackb

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)
 
A

Alan Beban

jenhow said:
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
Click to expand...

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),Tbl1,2,0)

Alan Beban
 
J

jenhow

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)
 
Z

zackb

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.
 
H

Harlan Grove

Alan Beban wrote...
jenhow wrote: .... ....
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),Tbl1,2,0)
Click to expand...

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?
 
A

Alan Beban

Harlan said:
Alan Beban wrote...

. . .
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?
Click to expand...

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
 
H

Harlan Grove

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),Tbl1,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
Click to expand...
pasting into my posting. Thanks for pointing it out.
Click to expand...

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?
 
Top