Matching company names written differently

**archimaitreya** · 02-23-2016, 05:26 AM

Hi,

I have been struggling with this issue for more than a day now and if anyone could help me with this it'll be great.

I have two sets of company names and their respective fee, dept name and FY. In the attached excel, you'll see on the right side that I have entire data set for the ABC group and on the left side is how I want it to be placed.

I want with the name "ABC" in cell B2, the fee should automatically populate for the respective dept in the respective FY (Cell D2 to E4).

I know vlookup doesn't work here and have tried with match/index also. But nothing seems to work out.

Thanks in advance!!!

**shukla.ankur281190** · 02-23-2016, 05:32 AM

try

D2

Formula:

Please Login or Register  to view this content.

**archimaitreya** · 02-23-2016, 05:36 AM

Thanks,

But this formula does not take the company name. In this instance, there is only 1 company. But otherwise I have more than 600 companies. So if the formula doesn't recognize company name, I cant get the correct figures.

**shukla.ankur281190** · 02-23-2016, 05:38 AM

Can you upload file with some example???

**JohnTopley** · 02-23-2016, 05:41 AM

2 problems;

Company name is ABC Group in your example ?

There is more than one fee for each category: are fees to be summed?

**sandy666** · 02-23-2016, 05:47 AM

=SUMPRODUCT($H$2:$H$48,($G$2:$G$48=$B2)*($I$2:$I$48=$C2)*($J$2:$J$48=D$1)) and drag across

With your example result will be 0 because Company name in table is different then in column ($G$2:$G$48=$B2). ABC <> ABC Group
You should define any condition/s for company name
Use ABC Group in B2 and you will see result

**archimaitreya** · 02-23-2016, 05:51 AM

I have uploaded the file. And the fee needs to be summed up. the difference in the name is the only challenge here which i need help on.

**shukla.ankur281190** · 02-23-2016, 05:57 AM

First Fill the B column with concern company name then

Try

D2

Formula:

Please Login or Register  to view this content.

AND DRAG DOWN AND TOWARDS THE CELLS

**archimaitreya** · 02-23-2016, 06:02 AM

Awesome!!!! this worked!!!! thanks a ton!!!

**shukla.ankur281190** · 02-23-2016, 06:04 AM

Thanks for feedback and Happy to help you.

Please add reputation by clicking on star * icon left corner on your screen which one really helps you and mark thread as solved.

See the signature for appropriate action pls.

**JohnTopley** · 02-23-2016, 06:11 AM

Try

=SUMPRODUCT($H$2:$H$100,($G$2:$G$100=OFFSET($B$2,INT((ROWS($1:1)-1)/3)*3,0,1,1))*($I$2:$I$100=$C2)*($J$2:$J$100=D$1))

Company names in full as per post #6

Only enter company name once as per your sample

Matching company names written differently

LinkBack

Thread Tools

Rate This Thread

Display

Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Re: Matching company names written differently

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] deleted the post

Matching misspelled or differently spelled names

pulling data from differently written names

Making a list of company names

Need help to display company names

counting company names

[SOLVED] calculations to be done differently based upon company name

Bookmarks

Bookmarks

Posting Permissions