Vertical Lookup is one of the commonly used MS Excel functions. But it has limitations in that the main search criterion needs to be in the first column. However by using a combination of MATCH and INDEX, you can return values from an array regardless of what information is in the first column of the array. Follow our example below as we explain how you can use MATCH and INDEX as an alternative to the Vertical Lookup.
MATCH: Returns the relative position of an item in an array that matches a specified value in a specified order.
INDEX: Returns a value or reference of the cell at the intersection of a particular row and column, in a given range.
Applies To: Excel 2003, 200 and 2010
- Reference will be made to the screen shot below. We are going to retrieve the Commission Rate for P9
2. Select cell F5
3. Select as below
4. Select as below
- In the first option the data array is only based on one data range
- In the second option the data array is based on multiple data ranges
5. Enter as below
6. Select OK
7. The answer will be 27% as given below
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
