VLOOKUP or INDEX or IF formula help

[Chimps]

I currently am working on a sheet to track training dates for managers within an organization. All of the training is tracked via electronic training records system that can generate reports of who has completed a course. In the past, we have run separate reports for each course, but the ability exists to run a single report for multiple training courses. With separate reports, I have a VLOOKUP built to track who has completed each course, but in an effort to save time and resources, I would like to grab the information from a single report for multiple people. I've attached sample sheets of the manager list as well as the report from the database. In theory, the manager's list would grab the completion date for each course from the training report. I can't figure out how to get the date to pull from one to the other. I can get an "ISNA(VLOOKUP)" type formula to put an "X" in each column, but I can't seem to get the actual completion date to populate for each course.

[#2 · Accepted solution]

Hi,

In general Power Query do such job quite good. If you have one file with the list of managers, another one is training report, you may merge it, pivot on training course and return resulting table into the sheet. As the simple sample here are 3 files attached.

If with formulas that could be

=IFNA(INDEX('[training report.xlsx]Sheet1'!$D$2:$D$37,MATCH(1,INDEX(($A2='[training report.xlsx]Sheet1'!$G$2:$G$37)*(D$1='[training report.xlsx]Sheet1'!$B$2:$B$37),0),0)),"")

or

=IFNA(INDEX('C:\Test\[training report.xlsx]Sheet1'!$D$2:$D$37,MATCH(1,INDEX(($A2='C:\Test\[training report.xlsx]Sheet1'!$G$2:$G$37)*(D$1='C:\Test\[training report.xlsx]Sheet1'!$B$2:$B$37),0),0)),"")

with closed training report file. Also attached (with "raw" in name).

[#3]

Oops, only 3 attachments are allowed. Here is the latest file.

[#4]

Sergei,

Thanks so much for the help! When I tried to enter this into the manager list file, I can't seem to figure out why it is giving me an error (well, I think it's giving me a "0" answer, as it shows 1/0/1900 briefly before becoming a blank cell. I have attached a screenshot that shows the sheet that I am trying to use this on, and you can see that D2 is blank with the formula entered. I can make it work on the sample sheets that I sent in the original post without an issue, but when I try to bring that formula (updating the link to the proper reference sheet) into the master list, it won't give me the date that I need. Here is the formula that I have in D2 currently:

=IFNA(INDEX('[district training report.xlsx]report'!$D2:$D774,MATCH(1,INDEX(($A2='[district training report.xlsx]report'!$G2:$G774)*(D$1='[district training report.xlsx]report'!$B2:$B774),0),0)),"")

And attached is a snip of what it looks like for me. Any help figuring out why I am getting what I guess is a "zero" value is appreciated.

[#5]

Hard to say without the file. Perhaps data is not matched. From the bit structured formula

=IFNA(INDEX('[district training report.xlsx]report'!$D2:$D774,
           MATCH(1,INDEX(
                       ($A2='[district training report.xlsx]report'!$G2:$G774)*
                       (D$1='[district training report.xlsx]report'!$B2:$B774),
            0),0)),
    "")

try to check only MATCH part as

=MATCH(1,INDEX(
       ($A2='[district training report.xlsx]report'!$G2:$G774)*
       (D$1='[district training report.xlsx]report'!$B2:$B774),
   0),0)

if it returns some number or error. If error try to compare the cells directly like

=A2=G15

In general your formula is correct if only no error in file/sheet name