How To Find The Column Number Of An Item In An Array In Excel

In this tutorial we are going to see how we can locate a value within a data range and then return the header information for the location of that value.

You can download the file here and follow along. When you get a preview, look for Download in the upper right hand corner.

So I have a data range like this:

I want to be able to select a name from the drop down list and have it tell me what team the person is on:

To do this we need to use the INDEX function along with the MAX, IF, and COLUMN functions in an array formula that is structured like this:

{=INDEX(Headers,MAX(IF(data=Name,COLUMN(data)-COLUMN(E2)+1)))}

Notice that this is an array formula by the curly brackets, and must be entered using Ctrl+Shift+Enter.

I’m also using names in my formula to make it easier to understand. The named references are as follows:

One item to note. The basis of this formula is the INDEX function. Normally the INDEX function has three primary arguments in its syntax:

=INDEX(array,row_num,[col_num])

In our formula, the array is “Headers” which is E2:H2, and the [col_num] is MAX(IF(data=Name,COLUMN(data)-COLUMN(E2)+1))).

Notice that we have omitted or ignored the row_num argument of the INDEX function. One interesting fact about the INDEX function is that, if the array is a horizontal list, as in our example, you can just enter the column information as the second argument and Excel will interpret it properly. There is no need to insert either a blank, like this:

{=INDEX(Headers,,MAX(IF(data=Name,COLUMN(data)-COLUMN(E2)+1)))}

Or a “1” to indicate row 1 of the array (since it only has one row) like this:

{=INDEX(Headers,1,MAX(IF(data=Name,COLUMN(data)-COLUMN(E2)+1)))}

So now that I’ve teased you a bit, please take a few minutes and watch the video so you can see exactly how and why the formula works as well as it does!

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply