Excel INDEX MATCH Formula to Pull Nth Value

May 4, 2017

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.  

 

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

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