VLOOKUP and Concatenation

  • Thread starter Thread starter PAL
  • Start date Start date
P

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.
 
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
 
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
 
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.
 
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
 
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.
 
Back
Top