VLOOKUP for multiple values
Sometimes it seems like I use the Excel VLOOKUP formula every day for one task or another. However it's not very helpful if you want to match up multiple values from two data sets. This INDEX formula works in effect as a multi-value VLOOKUP formula.
In the example shown in the screen grab below, the formula is used to find where in the table in columns G to J, entries from column G to I match those in the table in columns A to C. So in filling in the roster on the left, you can find that it's Tom Seaver who is a pitcher in New York for the team called the Mets. When entered in cell D4 the formula shows that the roster on the right does not list any first basemen for the Los Angeles Angels.
This is an array formula so you need to press CTRL+SHIFT+ENTER when entering in the first cell. You should see brackets appear around the formula. Just pull down using CTRL + D as usual.
I got the idea for this formula on this site: