Power Pivot - VLOOKUP equivalent to lookup all cells in a column

Back on December 27, 2015, the Tip of the Night (and the related YouTube demonstration video) showed how to use the LOOKUPVALUE function in Power Pivot to approximate the VLOOKUP formula in Excel. The LOOKUPVALUE function is designed to look up multiple specified strings - but not all of the values in a single column. You can use the RELATED function in Power Pivot to accomplish this. Keep in mind that Power Pivot gives you the ability to run calculations for far more data than MS Excel can handle. If you're trying to analyze data and it's taking forever to run a formula, or simply crashing, consider the DAX (Data Analysis Expressions) functions you can use in Power Pivot. The same tasks can be completed in moments in Power Pivot that may take 15 minutes or much longer in Excel.

1. Power Pivot should be available as an add-in for all users of MS Excel 2016. Go to File . . . Options . . . Add-ins . . . Manage: COM Add-ins, and Click Go. You'll see you have the option to to check off Power Pivot.

2. PowerPivot will show up as a separate tab on the MS Excel Ribbon. On this tab go to Manage, and open the Power Pivot application.

3. Next, on the Home tab, go to From Other Sources, and assuming that you have two Excel files that you want to review, scroll down and select 'Import data from an Excel file'

4. Simply browse to the Excel files you want to review, and import them in the wizard one by one. You should now have two tables on separate tabs in Power Pivot. Notice that you have the option to filter the worksheets down to only the data on them that you want to import.

5. Next go to the Design tab and select Manage Relationships. In this example, I'm assuming that there unique values in both tables - or it's possible to create a one to many relationship. The RELATED function will only work if one table has a field with a unique ID of some kind that is not repeated.

6. Create a relationship selecting a unique ID field that both tables have in common. If one of the tables does not have unique values in this field, you'll be given a warning that many to many relationships are not supported. Filtering data in one table so that only unique values in one field are displayed to will not avoid this problem.

7. In this example I have created a relationship between the playerID fields on two tables which show the batting stats for players in the 1986 regular season and the 1986 post season.

8. In order to display in the column on the far right, the number of home runs hit by each player during the 1986 World Series, alongside his batting stats from the regular seasons, select the top cell below Add Column and begin entering =RELATED( . At this point you'll be given the option to select any field in the related table. So in this example we just click on BattingPost[HR], and then enter a closing parentheses to complete the function.

You'll notice even with a million rows of data or more the function generates almost instantaneously, and will be entered for the entire column without having to select cells below and press CTRL + D, or take any other action.

Look at that! Gary "The Kid" Carter hit 27 home runs in 1986, and then hit two more in the series!