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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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