- Set up your two columns with headers in columns A and B in Excel. Column A is your master list which is the total list of items you have. Column B is your subset list which is the list of items you want to know are in Column A, your master list.
- Place your cursor in column D.
- Pull up the VLOOKUP function in D2 by clicking the fx below the menu bar and select VLOOKUP.
- The Lookup_value is the first value from the subset list column (column B). For example, we want to know of those who downloaded the update file (master list) which ones have Registrar's Office (subset).
- Select B2 for the Lookup_value because this is our subset - those who have Registrar's Office.
- Place a $ before the B in this value to make the field dynamic. This allows you to copy the forumula to the corresponding cells.
- The Table_array is your master list.
- Select A2 through the last populated cell in the column.
- Place a $ before the cell number and the number in the Table_array. For example $A$2:$A$1000.This allows you to copy the forumula to the corresponding cells.
- The Col_index_num is a blank column after your master list column and subset column and is always a number not the column letter.
- Place a 3 for the Col_index_num.
- Range_lookup will always be FALSE for an exact match.This is how your spreadsheet looks:
- Hit Enter to process.
- Copy the formula to cells equaling the master list column.
- You now have a column that shows some cells with #REF! and some cells with #N/A. The #REF! is your Yes cell and the #N/A is your No cell.
- To make this easier to read, copy the VLOOKUP column to an empty column and paste special (value).
- Use the Replace feature to change the #REF! to Yes and the #N/A to No for all cells in the column. This is how the spreadsheet looks:
You can delete the VLOOKUP column if you like.
- You now know who downloaded the file and had Registrar's Office.
- Remember the master list and the subset list can be anything for which you are looking.
Connect and collaborate with fellow Blackbaud users.