vlookup for several criteria

U

Ulli8

New Member
Joined
Jul 23, 2014
Messages
12
Hi,

I have a master spreadsheet that needs to pull information from a report (SHE Mand Courses 25Jul14)

I have an employee list with columns for various courses in my master sheet.
I have a report from which I want to pull the end date when the course was completed and otherwise the cells must stay blank.

The identifiers are:

  1. employee id number (column H on master sheet and column E on report source) and
  2. The course name (for every course there is an extra row on the report source sheet in column D). On the master sheet the course names are at the top of columns K to O in row 10.
  3. The score against it, which is the proof that the course was completeed (column M on the report source sheet)

When I simply asked it to return yes or no to see who completed their courses it worked fine, but I need it to return the end date in column F on the source sheet for that course in that row with that employee id, and I don't know how to do it.
The below formula returns one date for every course someone has completed.
It goes into vlookup and finds all the courses for one person and only uses the date it finds in the most bottom row of that employee. It does not in the vlookup stay in the row mentioned in the criteria range 2 and criteria 2 only). I don't know how to add criteria range 2 and criteria 2 and criteria range 3 and criteria 3 into a vlookup / index function to return the date of the associated course only, so I have the correct date for each course.

I think this would require an index function but have never written one before, and in order for it to work in the added criteria the range will need to be expanded to E$3:M$2000 probably too.

=IF(COUNTIFS('SHE Mand Courses 25Jul14'!$E$3:$E$2000,H13,'SHE Mand Courses 25Jul14'!$D$3:$D$2000,K$10,'SHE Mand Courses 25Jul14'!$M$3:$M$2000,">0")=1,VLOOKUP(H13,'SHE Mand Courses 25Jul14'!E$3:F$2000,2),"")

Any advice is greatly appreciated.

Regards,

Ulli
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Hi Blunder 1, I am not familiar with that.

Aladin,

Below is report source sheet and wanted outcome of data transfer on master sheet:

[TABLE="width: 510"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Report source sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Course Name[/TD]
[TD]Employee ID[/TD]
[TD]EndDate[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Environmental Essentials[/TD]
[TD]10[/TD]
[TD]01-Jul-14[/TD]
[TD]95%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Workstation Safety Plus[/TD]
[TD]10[/TD]
[TD]03-Jul-14[/TD]
[TD]90%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Workstation Safety Plus[/TD]
[TD]30[/TD]
[TD]29-Jun-14[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Environmental Essentials[/TD]
[TD]30[/TD]
[TD]06-Mar-14[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Health and Safety Induction[/TD]
[TD]20[/TD]
[TD]02-Jul-14[/TD]
[TD]93%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Environmental Essentials[/TD]
[TD]20[/TD]
[TD]14-Feb-14[/TD]
[TD]88%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Health and Safety Induction[/TD]
[TD]40[/TD]
[TD]03-Jul-14[/TD]
[TD]80%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Environmental Essentials[/TD]
[TD]40[/TD]
[TD]05-Jun-14[/TD]
[TD]80%[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 756"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]MASTERSHEET[/TD]
[TD="colspan: 4"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Number below[/TD]
[TD]H[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Res ID[/TD]
[TD]Environmental Essentials[/TD]
[TD]Health and Safety Induction[/TD]
[TD]Workstation Safety Plus[/TD]
[TD]Asbestos Awareness[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]10[/TD]
[TD]01/07/14[/TD]
[TD] [/TD]
[TD]03/07/14[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]20[/TD]
[TD]14/02/14[/TD]
[TD]02/07/14[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]30[/TD]
[TD]06/03/14[/TD]
[TD] [/TD]
[TD]29/06/14[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]40[/TD]
[TD]05/06/14[/TD]
[TD]03/07/14[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes sense.

Regards,

Ulli
 
Upvote 0
Define first Ivec using Formulas | Name Manager as referring to:
Rich (BB code): 
=ROW(report!$E$3:$E$10)-ROW(report!$E$3)+1

Master

H13, control+shift+enter (CSE), not just enter, and copy down:
Rich (BB code): 
=IFERROR(INDEX(report!$E$3:$E$10,
  SMALL(IF(FREQUENCY(IF(report!$E$3:$E$10<>"",
  MATCH("~"&report!$E$3:$E$10,report!$E$3:$E$10&"",0)),
  Ivec),Ivec),ROWS(H$13:H13))),"")

K13, CSE, copy across, and down:
Rich (BB code): 
=IF($H13="","",IFERROR(INDEX(report!$F$3:$F$10,
  MATCH(K$10,IF(report!$E$3:$E$10=$H13,report!$D$3:$D$10),0)),""))

See the workbook that implements the foregoing:
https://dl.dropboxusercontent.com/u/65698317/Ulli8%20reaarange%20data.xlsx
 
Upvote 0
Hi Aladin,

Thank you so much for this! I am embarrassed to say that I did not make it clear that the employee ID is already given in my master sheet, which is an HR report, and needs to be matched to the one on the academy course report, but I am sure I will need the ivec code some time in the future.

The date transfer formula works very well now, there is only one thing, and that is that it returns 1 Jan00 for cell values of zero on the source report sheet, and it does not make sure that the cell in column M from the source report sheet (the score) is populated. When this cell is not populated (i.e. if there is no score) then it means people only looked at the course but did not complete it. Would there be a fix for this to maybe squeeze in another condition with reference to column M on source report sheet?

I show below what I mean:



[TABLE="class: cms_table, width: 510"]
<tbody>[TR]
[TD]Report source sheet[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Course Name[/TD]
[TD]Employee ID[/TD]
[TD]EndDate[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Environmental Essentials[/TD]
[TD]10[/TD]
[TD]01-Jul-14[/TD]
[TD]empty cell[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Workstation Safety Plus[/TD]
[TD]10[/TD]
[TD]03-Jul-14[/TD]
[TD]90%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Workstation Safety Plus[/TD]
[TD]30[/TD]
[TD]29-Jun-14[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Environmental Essentials[/TD]
[TD]30[/TD]
[TD]06-Mar-14[/TD]
[TD]100%[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Health and Safety Induction[/TD]
[TD]20[/TD]
[TD]02-Jul-14[/TD]
[TD]93%[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Environmental Essentials[/TD]
[TD]20[/TD]
[TD]14-Feb-14[/TD]
[TD]88%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Health and Safety Induction[/TD]
[TD]40[/TD]
[TD]03-Jul-14[/TD]
[TD]empty cell[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Environmental Essentials[/TD]
[TD]40[/TD]
[TD]05-Jun-14[/TD]
[TD]80%[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="class: cms_table, width: 756"]
<tbody>[TR]
[TD]MASTERSHEET[/TD]
[TD="colspan: 4"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Number below[/TD]
[TD]H[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Res ID[/TD]
[TD]Environmental Essentials[/TD]
[TD]Health and Safety Induction[/TD]
[TD]Workstation Safety Plus[/TD]
[TD]Asbestos Awareness[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]10[/TD]
[TD]cell should be empty[/TD]
[TD][/TD]
[TD]03/07/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]20[/TD]
[TD]14/02/14[/TD]
[TD]02/07/14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]30[/TD]
[TD]06/03/14[/TD]
[TD][/TD]
[TD]29/06/14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]40[/TD]
[TD]05/06/14[/TD]
[TD]Cell should be empty[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Many many thanks again! You are a genius :)

Ulli
 
Upvote 0
K13 (modified), CSE:
Rich (BB code): 
=IF($H13="","",IFERROR(INDEX(report!$F$3:$F$10,
  MATCH(1,IF(report!$E$3:$E$10=$H13,IF(report!$D$3:$D$10=K$10,
  IF(ISNUMBER(report!$M$3:$M$10),1))),0)),""))
 
Upvote 0
Aladin,

This works an absolute treat, and makes me very happy indeed :)

Thank you so much for turning my sheet into a first class tool that is now very smoothly operating!!!

Big thumbs up to you.

Best regards,

Ulli
 
Upvote 0
Ulli8 said:
Aladin,

This works an absolute treat, and makes me very happy indeed :)

Thank you so much for turning my sheet into a first class tool that is now very smoothly operating!!!

Big thumbs up to you.

Best regards,

Ulli
Click to expand...

You are welcome. Thanks for the kind feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
230
etaf
etaf
M
  • Question Question
VLOOKUP help - return value if two partial matches are met (I think??)
Replies
5
Views
720
MountainFog
M
S
  • Question Question
Creating new sheets from template sheet plugging data into new sheets with particular criterias
Replies
1
Views
669
mumps
mumps
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
508
Peter_SSs
Peter_SSs
T
  • Question Question
Pull data from multiple sheets that match varying criteria
Replies
1
Views
661
bobaol
B

Forum statistics

Threads
1,223,227
Messages
6,170,849
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top