Match data from both sheets column and join them to another sheet column

[Marclem]

Hi Everyone,

Will try my best to explain my situation.

Below I have on column A data that repeats itself like AACE001N and so forth.

In the same excel I have another sheet with data on column A which I need to match from the other sheet column A and then copy the data to sheet 1 column B.

For example: everytime on sheet1 I see AACE001N then my column B same row will have data as AACE001N-WA-GH-Closed

I am also attaching the excel data. Thank you for your support!

-

 

[Andrzejek]

Wouldn't you just use a simple VLOOKUP in Excel?


---- Andy

There is a great need for a sarcasm font.

 

[Marclem]

Hi Andy,

I am still researching here, below what I got so far but when I git enter nothing happens:

 

[Marclem]

Also I tried using index array but got N/A error, below a screenshot (maybe is because my column B data have no spaces, it has a "-" after each word?):

 

[Andrzejek]

I tried to play with your Excel, but I had the same issues.
You have some links in your file, not the values:

That may be part of the problem.
I was trying to do Copy / Paste to find some data in your Excel file by hand, and I could not do it... :-(


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

You don't need concatenate...
[tt]
SHEET2!B1: {=INDEX(Sheet1!A:A,MATCH([@AwardNo],LEFT(Sheet1!A:A,LEN([@AwardNo])),0),1)}
[/tt]
...entered as an Array Formula (ctrl + SHIFT + ENTER)

BTW, it takes a while to calculate. Go get yerself a cup of coffee.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

WOW!!!

You're concatenating data from 9 columns for 1243 rows from an external workbook. And then waiting for all the links to calculate. Tic, tic, tic...

Must have taken you quite a while to perform the concatenation and then wait around each time you get new data, not to mention being sure you've accounted for every row. Right?

VERY AKWARD! TIME CONSUMING!

You could accomplish the data acquisition via MS Query, for instance. Set up the query in just a few minutes and refresh might take 5-10 seconds.

Ask me how, if need be.

BTW, I see you have 61 unmatched AwardNo values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

I'm with Skip, built-in power query allows to complete the task without formulas, much faster. Having both ranges as tables, in single query steps you can (1) duplicate second table's column, (2) split second column with delimiter, extract first part, (3) merge first table with left-outer join, using first table column and second table data in new column, (4) expand merged data, remove unnecessary columns, (5) replace 'null' (if no match) with custom text, (6) output data to workbook.

combo

 

[Marclem]

Hi Everyone,

I was able to get it working, did the following, data I need is on column D:

Thank you all again for your support!

Marc Nascimento

 

[SkipVought]

@ Marclem, it is not satisfying for you to simply state, "I was able to get it working..." after getting various tips leading toward a solution.

Our members want to know exactly what you did to obtain a solution. How did you get the data in column A, while column B appears to be the data from Sheet1?

It seems much more indirect than the solution I posted to be applied to the Sheet1 New column.

Help us out.

Sheet1
[pre]
Final
AACE001N-WA-GH-Closed
ABBOT001N-LAC-HT-Closed
ABBOT002N-LAC-HT-Closed
ABBOT003N-LAC-HT-Closed
ABBOT004N-LAC-HT-Closed
ABBOT005N-AS-IN-Closed
[/pre]

Sheet2
[pre]
AwardNo New
AACE001N =INDEX(Sheet1!A:A,MATCH([@AwardNo],LEFT(Sheet1!A:A,LEN([@AwardNo])),0),1)
AACE001N
AACE001N
AACE001N
AACE001N
AACE001N

[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein