Search Text in 2 columns, if match get cell and insert into second sheet

Hello,

I've been looking through the forums for a similar situation.

I copy an HTML document to the second page. It is a college degree evaluation, so each person will have a differing amount of rows.

Cells in the first page will search a specific column in the second page. If that matches, it will see if the second column in that row matches a text string. Finally, if both match the search criteria, it will read a third column in the row where the search matches and place it into the cell.

In the document I am providing, The first search (D15 on the Page DegreePlan!)is on "CS" for Computer Science in Column K. When it finds the first row where "CS" is in Column K, I want it to see if "1361" is in Column L.
IF both are true, then I want to concatenate the row number cell in Column Q (ie T for Transfer or R residence, etc) and Column P (Grade. some grades are blank, but the student is registered for it)

Works for a known row number. I need to work say if classes were added and it was pushed to row 63 the next time the report were run.

=IF(AND(DegreeEvaluation!K59="CS",DegreeEvaluation!L59=1361),CONCATENATE(DegreeEvaluation!Q59,DegreeEvaluation!P59),)

Test Degree Evaluation.xlsx

Need to check something..... please hold....

First you need to do a bit of clean up in the second sheet, when you copy over HTML stuff you sometimes get some hidden non-printable characters.

If you go for example to K65 in the Evaluation sheet, and put your cursor at the end of the CS in the formula bar, you will see an extra space. To get rid of it, copy just that space, then do a CTRL+H and paste in the Find What field, leave the other field blank and click Replace All. It should have replaced it many times. Then select column L and format as General.

Now in the first sheet, create a table on the side of the courses and their respective 2 letter codes.

Now assuming that table is in L2:M7, use this formula in D15:

=IFERROR(TRIM(INDEX(DegreeEvaluation!$Q$59:$Q$197&DegreeEvaluation!$P$59:$P$197,MATCH(1,INDEX((DegreeEvaluation!$K$59:$K$197=VLOOKUP(A15,$L$2:$M$7,2,0))*(DegreeEvaluation!$L$59:$L$197=B15&""),0),0))),"")

copied down.

copy to other groups below.