VLOOKUP and Concatenation

[PAL]

I have two worksheets with data that I need. There are more than 100 rows.

The first worksheet has many columns. Let's say:

Column A is a model number: 12332
Column B is a site number: 10
A and B are essentially unique identifiers
Column C - F is other data that I will need.

The second workshet has many columns also. Let's say:

Column A is the model number: 12332
Column B is the site number but different: 10-Jones
A and B are essentially unique identifiers, differing from the first work
sheet in that "B" is a longer version from the first worksheet (10 vs.
10-Jones).
Column C - F is other data that I will need.

How do I get C-F from the second worksheet lined up and onto the first
worksheet or vice versa C-F of the first onto the second.

 

[Jacob Skaria]

Try the below. Please note that this is an array formula. Within the cell in
edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in 'Formula Bar' you can notice the curly braces at
both ends like "{=<formula>}"

From sheet1 to sheet2
with Sheet1 row 1
Col A Col B
12332 10

=INDEX(Sheet2!$C$1:$C$100,MATCH(1,(Sheet2!$A$1:$A$100=A1)*(LEFT(Sheet2!$B$1:$B$100,FIND("-",Sheet2!$B$1:$B$100)-1)+0=B1),0))

From sheet2 to sheet1
with row 1 data in sheet2 as
Col A Col B
12332 10-Jones

=INDEX(Sheet1!$C$1:$C$100,MATCH(1,(Sheet1!$A$1:$A$100=A1)*(Sheet1!$B$1:$B$100=LEFT(B1,FIND("-",B1)-1)+0),0))

If this post helps click Yes

 

[Jacob Skaria]

If Col A is a unique identifier why dont you use normal VLOOKUP. Do you have
duplicates within Col A...


If this post helps click Yes

 

[PAL]

Working on it now. Can't seem to get yet. The combination of Column A and B
make it unique. The data from the sheets come from two Dbs, the only
difference being the way column b is captured. Also, there is a " " between
the two, not "-" so I have tweaked your formula.

 

[Jacob Skaria]

I have tried this *** array formulas *** with the given below examples and it
works.. Ofcourse I tried with 10-Jones without space; but i think that doesnt
matter since we are using +0 to convert that to a numeric...

If this post helps click Yes

 

[pshepard]

Hi Pal,

paste Jacob's formula (modified below) into Sheet1!$G$2 to pull column C,
Sheet2 into column G Sheet1:

=INDEX(Sheet2!$C$1:$C$100,MATCH(1,(Sheet2!$A$1:$A$100=$A2)*(LEFT(Sheet2!$B$1:$B$100,FIND(" ",Sheet2!$B$1:$B$100)-1)+0=$B2),0))

paste Jacob's formula (modified below) into Sheet2!$G$2 to pull column C,
Sheet1 into column G Sheet2:

=INDEX(Sheet1!$C$1:$C$100,MATCH(1,(Sheet1!$A$1:$A$100=$A2)*(Sheet1!$B$1:$B$100=LEFT($B2,FIND(" ",$B2)-1)+0),0))

These formulas were changed to reflect the row that they are pasted into,
and to find " " [space] instead of "-" [hyphen].

As Jacob pointed out, these are array formulas, so after pasting a formula
into a single cell, use the F2 button to edit the cell, then press the
CTRL+SHIFT+Enter buttons so that curly brackets start and end the content in
the cell. After the formula has been entered for one cell in this way - then
the formula can be copied as usual for the 100 rows.