Vlookup if a period matched.

[Warren Keogh]

Hi

I have two tables of data. One shows periods (month 1 , month 2 etc), Sales person name , sales person code, commission earned.

The second table shows periods (month 1 , month 2 etc), Sales person name , sales person code, total pay for the month.

Some sales people do not earn commission in each month. I want to add a formula in table 2 which for each month will check if a sales person earned commission in that month and if so return the amount of commission. If there is no commission it would return 0.

I have tried Vlookups with Index or Match but its not pulling in the data correctly.

Any help appreciated.

WK

 

[NARAYANK991]

Hi Warren ,

Given the nature of your requirement , you will get a precise solution if you upload your file , even if it is a sample file with just the data layouts / tables.

Narayan

 

[Warren Keogh]

Hi Thanks Sample file uploaded.

regards

WK

 

[prasaddn]

Hi Wk,

You can try the below CSE formula:

=VLOOKUP(A2&B2,CHOOSE({1,2},'Table 1'!A:A&'Table 1'!B:B,'Table 1'!D:D),2,0)

on pressing Cntr+Shift+ Enter, excel will automatically, add {} braces.

You can get the detailed expanation in the following article:
http://chandoo.org/wp/2014/10/28/multi-condition-vlookup/

Regards,
Prasad DN

 

[azumi]

Pls see the file using array formula, means when you ENTER you need to confirm press CTRL-SHIFT-ENTER button together and then copied down as necessary

 

[John()]

ok i got same results as lad above just used different formula ...... did notice when i was doing error checks that you have a lot of duplicate data in period 12 section eg employee 10002 gets 11,676.48 and 1889.02 commision and then another employee 10002 gets 1,343.45 and 1889.02 commision
This is the case for about 20 employes in total
have uploaded file and highlighted relevent section in period 12

 

[Warren Keogh]

ok i got same results as lad above just used different formula ...... did notice when i was doing error checks that you have a lot of duplicate data in period 12 section eg employee 10002 gets 11,676.48 and 1889.02 commision and then another employee 10002 gets 1,343.45 and 1889.02 commision This is the case for about 20 employes in total have uploaded file and highlighted relevent section in period 12

 

[Warren Keogh]

John Thanks for you help. Yes I noticed the period 12 had additional entries. It was a bonus run so I had to consolidate the data first. Good idea combing the employee number and period number.

Azumi Thanks for your help formula worker great.

Prasad Thanks for your help and the link to the article.