Index and match formulae with names, costs and new dept | General Excel Questions & Answers

Index and match formulae with names, costs and new dept

Mel Paes

Member

Members

Dashboards

Power Query

Power Pivot

Excel

Word

Outlook

Excel Customer Service

Forum Posts: 8

Member Since:
May 30, 2015

Offline

October 3, 2016 - 8:26 am

Hi Excel Geeks

This is another one of my index match scenarios - that is triangulating me.

I am trying to match the first name and surname on one list to a second list of names and if they match to select the new department they work from the worksheet labelled ‘list of names with costs and new department (de)’ and then pick the cost against the matched name from the first list – ‘Names to match costs and new depts’ and insert the costs under the correct new department. I have attached a sample file to clarify what I am trying to achieve.

Any suggestions of doing this in an efficient way would be greatly appreciated.

Regards

Mel

See Answer

SunnyKow

Malaysia

Member

Members

Dashboards

Forum Posts: 704

Member Since:
June 25, 2016

Offline

October 4, 2016 - 12:46 am

Hi Mel

Your formulas in column D and E is not correct. When there are two Andy you will get the wrong answer (E6 and E8).

I have included the correct formulas in the attachment (refer yellow cells). Note that I have created a helper column (I) to extract the new department.

You will need to change the range to suit your needs.