Index Matching

Red Christmas Retail Facebook Post

Have you ever wanted to create a formula in Excel that intelligently pulls from a data list? For example, when I was working with restaurant owners, I was always looking for an efficient way to maintain an inventory list that directly feeds information to the recipes in real time. In the past, I used a command called vLookup, but found it cumbersome for a couple of critical reasons. The architecture of the dataset is constrained to vertical columns where the lookup value is to the right of the label. The formula also breaks whenever a column is added or removed.


A few years ago, I was introduced to the Index/Match convention and I never looked back. Here is the syntax:

                        =INDEX(array,MATCH(lookup_value,lookup_array,match_type))


What I love about this function is that it is both more flexible than vlookup, and more robust when you have to add to the dataset. To see this functionality in action, watch the video.  

 

0 comments

There are no comments yet. Be the first one to leave a comment!