top of page

Excel INDEX MATCH Formula to Pull Nth Value


You can use a modified INDEX MATCH formula to pull the second, third, fourth, the nth values from an array for the string you are searching for. So in this example:

=INDEX(B:B, SMALL(IF(A:A=D1,ROW(A:A)),3), 1)

. . . we search for the value entered in D1 in column A, and pull the value that appears in row B. So when we search for 'New York' like this:

. . . we find the third New York city listed in column B. If we switch the '3' to a '4' at the end of the formula, we get the fourth city.

Note that this is an array formula so you have to press CTRL + SHIFT + ENTER when entering it and make sure that brackets appear around it.

Thanks to Mike Rickson for posting this formula here.


Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

​

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

​

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page