Excel Tip: How to Easily Remember the Index-Match Formula

Lots of experts tout Index-Match as a better look up formula than venerable Vlookup.  However, it's a little hard to remember its formula (which is a mash up of the Index and Match formulas).

Here's how I do it:

=INDEX(Range with values I don't have, (MATCH(Look-up value, Range with values I do have, “0″))

Everything I have goes in MATCH; what I don't have goes in INDEX.  For example:
  • Range with values I don't have = Range of product IDs
  • Look-up value = Product Name
  • Range with values I do have = Range of product names
The 0 at the end of the formula means you're looking for an exact match.  Check out the Index-Match article linked above for a fuller explanation.



No comments:

Post a Comment

Note: Only a member of this blog may post a comment.