Vlookup on multiple columns

pjandshelly

Board Regular
Joined
Jan 25, 2017
Messages
61
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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:
Code:
=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).
 
Upvote 0
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.
Code:
=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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top