Match vs. Choose

You need to have one data picking solution tucked safely into your Excel utility belt: a favoured tool that helps you pluck the one number you want out of a big set of data. If you've been following these posts you'll know that my favourite is Index but you're likely to have your own.

The role of Match inside Index

You'll regularly see Match combined with Index. Index finds the intersection of a particular row number and column number. In the example below I've given Index one row of data (that's one of the great things about Index, it can cope when given just one row or column) and told it to look in column 15.

Match's job is to work out that Mar-19 lies in column 15. So Match tries to find Mar-19 in the list of months and works out that Mar-19 sits in position 15. That's helpful as an input for the later Index function.

Note to play it safe with Match please get used to typing "False" or "0" (which amount to the same thing as far as Excel is concerned) into the back end. That means the formula will return you an error if it can't find the piece of data you asked it to look for. That's a good thing.

The same applies to Vlookup if you're a fan of that one. Always type "False" or "0" into the back.

Choose

Choose can provide you with a great data picking solution when you've got a very limited set of data. Imagine you were running three scenarios and had three data points. That might be a moment for Choose to shine.

Match and Choose compared side by side: Mr DJ I'd like a song please

The two formulas feel a little bit similar. Match is regularly used to help provide row and/or column inputs for Index. Choose is great as a data picking solution when you've got a very small data set. Here are the two compared side by side.

If we feed Match a piece of data Match will tell us what position that item occupies in the list. Choose feels like it might almost be the opposite of Match. If we tell Choose what position our data sits in, Choose will restore back to us the piece of data that sits in that position.

Match vs. Choose

Match sits inside of Index to point Index to which row or column we're interested in. Maybe Choose becomes an alternative data picking solution to Index, on the one day you've got the smallest data set in the world (say around 5 pieces of data) and you can't be bothered to weild your go-to data picking solution that day - which should be Index I reckon.

Match finds the row or column number the piece of data we're interested in sits in and it returns back a number to us. With Choose we feed it a number and it returns to us the piece of data that occupies that particular position in the list. One feels like the other turned upside down. But the first (Match) is a common input when we're data picking using Index. Choose is an alternative data picking solution to Index.

Now you're clear on the difference between the two! Happy modelling!