Vlookup on multiple columns

[pjandshelly]

I have a table of courses with the name of the student. Then I have another table that I am trying to get a Yes or No value based on if they took the class. I believe I need a vlookup function, but I am missing how to get that data.

Student Course
Tom Smith Math 101
Jerry Jones Math 101
Jerry Jones Science 101

Student Math 101 Science 101
Tom Smith Yes No
Jerry Jones Yes Yes

Thank you for any help.

 

[Joe4]

Here is one way.

Let's say that your data of studetns and courses is in the range A1:B4.
And let's say that they new table with formulas you are trying to build is in F1:H3
Then, place this formula in cell G2:

=IF(COUNTIFS($A:$A,$F2,$B:$B,G$1)>0,"Yes","No")

and copy all the way to H3 (so G2:H3 all have formulas).

 

[pjandshelly]

Awesome. Thank you for your help. Worked like a charm.

 

[Joe4]

You are welcome.

If you have a lot of data, and find that it runs slow sometimes, you may be able to speed it up by using specific range references, instead of entire column references, i.e.

=IF(COUNTIFS([COLOR=#ff0000]$A1:$A1000[/COLOR],$F2,[COLOR=#ff0000]$B1:$B1000[/COLOR],G$1)>0,"Yes","No")

Just make sure that you go down far enough to cover all your data, and make sure that the two ranges highlighted in red have the same row numbers.

 

[pjandshelly]

will do. Thank you again.