Retrieving multiple cell info from a worksheet 2 and show results on worksheet 1

I have posted this question on another Forum without much luck. Hopefully someone here can help. Lately, I have tried VLookup with Match/Index without much luck.
I maintain a worksheet containing approximately 15 items of our 73 employees' qualifications/certifications I call "Training". I use a separate program to access these qualifications. THAT program returns my query as an Excel worksheet which I can copy/paste into my Training workbook at sheet 2. Now, depending upon the particular employee, I will have between 15 - 30 rows for each employee with their Name, the course description, course number, their qualification(Q) or Certification(C), and expiration date. What I am trying to accomplish is to have Excel search the training records, by row and column. If the Name, course number, and whether they are qualified/certified(all three) are present, it would report that in the training summary(sheet 1). For instance, if I want to know if Johnny Jones is qualified to operate a fork truck, I want to search sheet 2(where the training records are kept) and if it finds Johnny(at say, row 74, column B) has the course number recorded(at row 74, column F) and has satisfactorily completed the training(recorded at row 74, column H) then it would put a "C" or "Q" in the in the cell on sheet 1 if all three conditions are met.The training worksheet doesn't always remain the same since people are adding/losing qualifications. So next week Johnny's fork truck qual might get moved and be recorded at row 78, etc. I have tried using VLookup, but I can't seem to get it to be precise enough. Hopefully someone is using this scenario and is willing to help me.
I use the IF/AND statement on sheet 1 to do this, but I can't figure out how to have Excel search across multiple sheets.
Thanks for your time.
Hello, again. So, I have been trying to get this to work. I am using the following on Sheet1 to tell me if someone is qualified: =IF(AND(B2="JONES, JOHNNY",H2="G157",J2="C"),"C","") and I can get it to work when I search sheet2, using single cell search, but if I try to search multiple rows/columns on sheet2 I get the error message: #VALUE!
=IF(AND(Sheet2!B2:B45="JONES, JOHNNY",Sheet2!H2:H45="G157",Sheet2!J2:J45="C"),"C","")
Hello, again. So, this is the current attempt: =IF(AND(Sheet2!B:B="JONES, JOHNNY"),IF(AND(Sheet2!H:H="G157"),IF(AND(Sheet2!J:J="C"),"C",""))) this returns a "FALSE"(without quotes). Not sure what that means. At least I am now searching entire columns. Any ideas?

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Try

=IF((Sheet2!B5:B47="JONES, JOHNNY")*(Sheet2!H5:H47="G157")*(Sheet2!J5:J47="C"),"C","")


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

John Topley, XOR LX:
You folks answered so quickly that I haven't had a chance to say "Thank you". I appreciate the quick responses and your time helping me.

So, I adjusted the row numbers in each of the columns to accurately reflect my info, and put my employee's name in place of Johnny's. The formula does have the curly brackets around it.
So, I put in this formula at W68 on sheet 1. It returns an empty ("") cell. However, I KNOW that at Row 225 this would be a true statement and a "C" should be placed in the cell.
=IF((Sheet2!B183:B282="Jones, Johnny")*(Sheet2!H183:282="G157")*(Sheet2!J283:J282="C"),"C","")

XOR LX - You mentioned that "That formula will only ever return "C" if row 5 satisfies the three criteria. The other 42 rows being referenced are redundant as far as that construction is concerned." I noticed that corresponds to the first row of the search criteria(B5:B47). Is it only true if it occurs in the first row of the search? I need it to search ALL rows(I have 8,539 rows of data).

John Topley, XOR LX:
You folks answered so quickly that I haven't had a chance to say "Thank you". I appreciate the quick responses and your time helping me.

So, I adjusted the row numbers in each of the columns to accurately reflect my info, and put my employee's name in place of Johnny's. The formula does have the curly brackets around it.
So, I put in this formula at W68 on sheet 1. It returns an empty ("") cell. However, I KNOW that at Row 225 this would be a true statement and a "C" should be placed in the cell.
=IF((Sheet2!B183:B282="Jones, Johnny")*(Sheet2!H183:282="G157")*(Sheet2!J183:J282="C"),"C","")

XOR LX - You mentioned that "That formula will only ever return "C" if row 5 satisfies the three criteria. The other 42 rows being referenced are redundant as far as that construction is concerned." I noticed that corresponds to the first row of the search criteria(B5:B47). Is it only true if it occurs in the first row of the search? I need it to search ALL rows(I have 8,539 rows of data).

If at least one row satisfies the criteria:

=IFERROR(INDEX(Sheet2!J5:J47,SMALL(IF((Sheet2!B5:B47="JONES, JOHNNY")*(Sheet2!H5:H47="G157")*(Sheet2!J5:J47="C"),ROW(B5:B47)-ROW($B$5)+1,""),1)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

A blank cell, but all the data has to be in one row. So, I don't want it to find the name in B182, the G157 in row H175, and the C in row 266 and mistakenly report him as "qualified". (It would have to find all three in row 225)

This formula ONLY works if all are in one row.

Post a file with your data (or sample of).

@XOR ....Yes ... me being overly complex (and missing the obvious!).

It looks as though the COUNTIFS statement works!!! At one time I had the following:
=IF(AND(Sheet2!B2:B45="JONES, JOHNNY",Sheet2!H2:H45="G157",Sheet2!J2:J45="C"),"C","")
Would that have worked IF and I say IF I had pressed Cntrl, Shift, Enter to activate the array?
I am wondering if you would help me further:
In sheet 2 there is a column K that tells me when Johnny’s qualification will expire and column L that tells me if he is <60/<30 days from expiration. Can I have Excel report that date, instead of the “C” if he IS within 60 days?

I have learned a lot from you two this morning/afternoon(read afternoon/evening depending upon your time zone). I want to thank you. I had this same question up on Stackoverflow.com for 5 days and no one responded. You folks were all over it.
Thanks again. I will be back to learn some more. Hopefully, one day I can help someone else.

Not sure if this is what you require:

=IF(VLOOKUP("Jones, Johnny",Sheet2!$B$4:$L$100,10,0) < TODAY()+60,VLOOKUP("Jones, Johnny",Sheet2!$B$4:$L$100,10,0),"")

Matches "name" ("Jones, Johnny") and if date in K (10th column from B) is less than 60 days from TODAY(), returns the date in K: otherwise returns blank.

Replace "Jones, Johnny" with cell as required e.g B1 in example below.

=IF(VLOOKUP(B1,Sheet2!$B$4:$L$100,10,0) < TODAY()+60,VLOOKUP(B1,Sheet2!$B$4:$L$100,10,0),"")

Try

=IF(AND(COUNTIFS(ATMS!B:B,"JONES, JOHNNY",ATMS!H:H,"G450",ATMS!J:J,"C"),VLOOKUP(B1,ATMS!$B:$L,11,0) < TODAY()+60),VLOOKUP(B1,ATMS!$B:$L,11,0),"")

This works on the assumption there is only ONE entry for "Jones, Johnny".

If found, COUNTIFS will return a 1 (TRUE) and VLOOKUP will return TRUE, so the AND(....) is TRUE and so we return the value in column L.


and 10 should be 11 (my error!)

It didn't work. I may not be explaining it correctly. Maybe I can go about this a different way. The program I am using to gather this information automatically puts the <60 in Column L when it comes due. Is it possible to tell Excel to display the "C" if Column L is "" but if it contains <60, display that instead? If I am using:
=IF(COUNTIFS(ATMS!B:B,"JONES, JOHNNY",ATMS!H:H,"G450",ATMS!J:J,"C"),"C","")
Could I use some form of:
=IF(COUNTIFS(ATMS!B:B,"JONES, JOHNNY",ATMS!H:H,"G450",ATMS!J:J,"C",ATMS!L:L,"<60"),"C","")
Where if C is present and <60 is not, it would display C. But if <60 in displayed at column L it would display <60?

Please post a file which represents your data.

Try

=IF(COUNTIFS(ATMS!B:B,"JONES, JOHNNY",ATMS!H:H,"G450",ATMS!J:J,"C",ATMS!L:L,"="&"<60"),"<60",IF(COUNTIFS(ATMS!B:B,"JONES, JOHNNY",ATMS!H:H,"G450",ATMS!J:J,"C"),"C",""))

This is the basic layout. I think if I could compare all four criteria(highlighted) and report, on sheet 1, either C or <60 that would work for me. It would give me an alert that his training is coming up.
Ok. I uploaded it. Don't know where it went.....(Johnny's Quals)

See post #21

I tried that one. It returns C even though I used a line item containing the <60 remark

Apart from the fact that everything in your sample was in the wrong range .... and the cell for <60 is "< 60" i.e. has space: so yes it will fail;.

If you correct all the "errors" you will find it works

=IF(COUNTIFS(ATMS!B:B,"JONES, JOHNNY",ATMS!H:H,"G450",ATMS!J:J,"C",ATMS!L:L,"="&"< 60"),"< 60",IF(COUNTIFS(ATMS!B:B,"JONES, JOHNNY",ATMS!H:H,"G450",ATMS!J:J,"C"),"C",""))

That one DID work, as you said. Sometimes, when I think I know, I don't really KNOW. I appreciate your patience and hanging in with me while I(meaning YOU) figured this out.

Good - glad it is sorted.

For future reference, it is better to have your posted file in the same format (ranges) as your "real" file: only include data required to solve the problem.

Issues like (not knowing of) the extra space in the "< 60" test will cause problems which is why posting of files is generally requested.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Obviously, come back if you need further help.

Ok. I will strive to make my examples as "real". I will mark it as solved and I will sure I will be back as I learn more about the power of Excel. Like I said, hopefully someday I will be able to help someone else.
Thanks, John.