Tag Archives: excel

Excel, a better vlookup (index, match).

A great video on getting around some of the annoyances of a vlookup:

http://www.businessinsider.com.au/excel-index-match-vlookup-video-how-to-2015-2

Update: After looking at this in a couple of projects here are some additional hints:

http://www.randomwok.com/excel/how-to-use-index-match/ has a great written resource on the subject and has simplified it down into a nice little formula:

=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

Although I find if working with zero’s or null objects sometimes you get thrown an NA error,
So in this case I use:

=IFERROR( INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” )), Default Error Value, usually 0 )

If you really want to dive in and see exactly how this works and why, check out this article: How to Use INDEX+MATCH With Multiple Criteria in 5 Steps