Good Morning,
I am working with a workbook that has two sheets. One titled Quals and the other titled ATMS. It is one I use to track the qualifications of the 73 people in my department. I am trying to have Excel return the course description, the date/time of the training and the location.
On the Quals sheet, I have the employees names listed in Col "G". The info I want is contained on the ATMS sheet in columns "H", "I", "M", "N". I am trying to get Excel to give me the info, if it is there, in the corresponding row for a particular employee on the Quals sheet.
The ATMS sheet is constantly changing as new quals are obtained or lost so I have to be able to search the entire sheet and if it finds the employee's name in column "B", AND the Event Start (column "M") has at date/time entry(MM/DD/YYYY HR:MIN) then Excel will report the info in column "H"(ID, text), column "I"(TITLE), column "M"(EVENT START) AND column "N"(BLDG/FL/RM) info into column "O" on the "Quals" sheet.

Try:

=IFERROR(INDEX('ATMS'!H:H,MATCH($G2,'ATMS'!$B:$B,0 )),"")

copied down.

Repeat similar formula for I, M, N by replacing the INDEX range

Thanks for replying NBVC. I put in the formula and it returns the first instance of the employee name/course title but not the training that is scheduled. Is there a way to tell it to check all criteria? ie: =IF(AND(AX24="C",AY24="C",AZ24="C"),M24,"")

Can you post a representative sample workbook showing what you are expecting?

Here you go. In this example, John Smith, Johnny Jones, and Ruby Butcher have scheduled training as seen on Tab ATMS. I would like to have that reported in the highlighted area of the Quals Tab.
Thanks for taking the time to look at this.

Attached Files

Training Sample.xlsx (94.9 KB, 11 views)

Try:

=IFERROR(INDEX(ATMS!H$2:H$100,MATCH(1,INDEX((ATMS! $B$2:$B$100=G27)*(ATMS!$O$2:$O$100<>""),0),0 )),"")


Note: This is an array formula, and although it doesn't need to be confirmed as a usual array formula, you will need to limit your ranges to defined sizes instead of full columns...

Thanks for replying, NBVC. I tried that, it returned the first instance of the employee, name, the Title of the training course. How about if we go about this a different way. maybe with a IF/AND statement. What if Excel searches ATMS column M, finds the Date/Time (formatted as:mm/dd/yyyy hr:mm:ss) AND matches the employee name in ATMS column B, AND employee name in Quals column M, returns the text in ATMS column I? Do you think that is a working scenario? I tried this one:
=IF(AND(ATMS!M389="MM/DD/YYYY","HH:MM:SS"),ATMS!I389)
and it returned a #VALUE! error. What did I do wrong?

My forrmula above should do that.

It returns the value from column H for each employee in column G of the Quals sheet (starting at G27) where there is a date in column O of ATMS sheet. Enter the formula in Quals!J27 and copy down.

to get Titles from ATMS sheet, change the (INDEX(ATMS!H$2:H$100 part of the formula to (INDEX(ATMS!I$2:I$100

It DID work in my example workbook. But when I tried to put it into my main workbook, it would return a blank cell. You said it was limited. Can you tell my by how much? My main ATMS worksheet has 6921 rows of info.

That should be no problem... Extending your range to 10,000 should be no problem.

All I changed is the range (10000). Excel returned a 0 (zero) in the cell
=IFERROR(INDEX(ATMS!I$2:I$10000,MATCH(1,INDEX((ATM S!$B$2:$B$10000=G27)*(ATMS!$O$2:$O$10000<>""),0),0 )),"")

Are there 0's or blanks in column I of ATMS where there is a name in column B and nonblank (date) in column O?

Without seeing the workbook, I would say where there is a match to G27 in ATMS column B and a non-blank in column O, there must be a blank or 0 in column I

Please see attached. G27 is highlighted on the Quals tab.
Thanks.

Attached Files

Quals (Rev 3).xlsx (298.0 KB, 10 views)

In your original sample file, the people's names were in G and they started in G27, that is why I referenced G27. Here you have the people's names in column A.

So since your first name seems to be in A23, then I would put this formula in G23:

=IFERROR(INDEX(ATMS!I$2:I$4961,MATCH(1,INDEX((ATMS !$B$2:$B$4961=A23)*(ATMS!$O$2:$O$4961<>""),0),0 )),"")

copied down.

Thank You, NVBC!! That DID work. I appreciate your help in getting me straight!! I noticed that Excel only reports the first instance of training listed. Some of them have multiple training sessions scheduled. Is there a way to have Excel report ALL instances of training? Maybe in a different cell, like putting an additional formula in cell H23?