If name from column g matches name from column a get data from column h and put it in column c

[cout3]

Hi guys

Sorry for such a long title , I hope the admins don't chase me down . That's pretty much what I want to do .
I found vlookup and it seems not that easy for a novice like myself . I am trying to update our CRM with a field from another dbf file , we had an older program and now we are trying to match the data . What I am basically looking for is this . In the first dbf I have :

company_name field1

company a data01
company b data02
............. ........
company z data99



Second dbf from the crm :

company_name field1

company a
company cb
...............
company zc

The names in the company_name do not match in exact terms in the 2 dbf's , meaning that the data is not the same , someone added a company name in the second dbf without adding it to the first dbf but for about 80% of them the company_name matches . All I want to do is copy the data from field1, from the first dbf and if the company_name matches put it in the field1 column on the second dbf , if the name doesn't match , move along to the next company_name field .
I am using the orbit crm updater , if it makes any difference to anyone , which I doubt .
Thank you for reading my message.

 

[Aladin Akyurek]

Let columns A and B on Sheet1 house the first 'dbf' from row 2 on.

Let column A on Sheet2 house the company names from the second or CRM 'dbf'.

In B2 on Sheet2 enter and copy down:

If on Excel 2007 or later...

=IFERROR(INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)),"Not Found")

Othwerwise...

=IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0)),INDEX(Sheet1!B:B,MATCH(A2,Sheet1!A:A,0)),"Not Found")

 

[cout3]

Yes the first formula worked , with a little tweaking , I think you meant A1 in the MATCH declaration instead of A2 . It was off by one row with A1 in there . Now it's working fine with one column of data but I have about 180 columns for each company name . What do you say ? I'm buying though , looks like it's my turn .
Thank you for your help anyway , you got me out of my misery . I searched all over this forum for vlookup , could find something related but didn't work in my case . Can I ask you why didn't you use vlookup in the first place ?

Thanks

 

[Aladin Akyurek]

Yes the first formula worked , with a little tweaking , I think you meant A1 in the MATCH declaration instead of A2 . It was off by one row with A1 in there . Now it's working fine with one column of data

That's great. A2 was assumed to be the cell housing the first look up value. If not, it needs adjusting as you've done.

but I have about 180 columns for each company name . What do you say ? I'm buying though , looks like it's my turn .

Would you care to state the problem in terms of say 3 columns?

Thank you for your help anyway , you got me out of my misery .

You are welcome. Thanks for providing feedback.

I searched all over this forum for vlookup , could find something related but didn't work in my case . Can I ask you why didn't you use vlookup in the first place ?...

Almost any VLOOKUP can be re-written in terms of INDEX/MATCH which is more flexible: You can do a look up in any direction; It's slightly faster; and it doesn't seduce you to use hard-coded result column numbers.

 

[cout3]

Would you care to state the problem in terms of say 3 columns?

I don't understand . What do you mean ? Maybe I messed up in explaining : I was trying to be simple in the beginning , so my idea was , let's try to match at least the first column in the beginning and then I'll move on from that .
The file actually has about 190 columns , first column being the company_name and all the other 189 contain the data related to each company . Now the row next to the company , B2 , worked beautifully with your formula , now I'm looking to expand it to all the adjacent columns to get all the data . I can always copy the contents and moving on to the next one manually which I will eventually do if nothing else comes up .
And you didn't tell me if it's beer or not .
Thanks man , like I said , you got me out of a big mess .

 

[cout3]

Oh yeah , I almost forgot , the formula added some '0' s in some columns and I had to replace them with an empty space , and the issue is that the data columns contain years , and where it's 09 , 08 , 10 , etc , now it's shows 9 , 8 and 1 .
I don't think that's such a big deal , there's not that many of them .

 

[cout3]

Oh yeah , I almost forgot , the formula added some '0' s in some columns and I had to replace them with an empty space , and the issue is that the data columns contain years , and where it's 09 , 08 , 10 , etc , now it's shows 9 , 8 and 1 .
I don't think that's such a big deal , there's not that many of them .

Edit:
Never mind , I fixed that with the match cell contents in the find&replace .

 

[Aladin Akyurek]

Edit:
Never mind , I fixed that with the match cell contents in the find&replace .

That's great.

If you want to retrieve all values in a record to which a look up value belongs to...

B1:

=IFERROR(MATCH(A1,Sheet1!A:A,0),"")

C1, copy across:

=IF(N($B1),INDEX(Sheet1!B:B,$B1),"")

 

[cout3]

Tried the C1 with the copy across , didn't work , I'm afraid I'm doing something wrong . I am however copying each column manually and I think this is a better idea because my boss can spot check them himself .
I'm not done yet but it's going pretty fast , faster that I expected , the import however is going in chunks of 10 so it's only a matter of time .
I'll keep you posted .

 

[cout3]

Yeah , I came to the conclusion that the C1 formula doesn't work . No data is brought over to any cell , I tried some variations of it none worked , it's like it's an invalid formula , nothing comes over , not even mismatched data . Hmmm could it be that it's not copied all the way through ? I doubt it .

Thanks