Last week I had an email from Mike asking how he can lookup a suburb in a range of columns and return the post code from the header row.

I imagine his data was a bit like this:

And in cell B9 he wants to find the post code for Herston.

One way is with this array formula:

=INDEX(B1:F1,,MIN(IF(B2:F5=A9,COLUMN(A:E))))

*Entered with CTRL+SHIFT+ENTER.*

Download the Excel Workbook and follow along. *Note: this is a .xlsx workbook, please ensure your browser doesn't change the file extension on download.*

Before we dive in, here are the syntaxes for the INDEX and IF functions as a reminder (I’ve crossed out the arguments we’re not using):

INDEX(reference,row_num,[column_num],[area_num])

`IF(logical_test, [value_if_true],[value_if_false])`

The INDEX formula is returning a reference to the cell in the first row for the column containing ‘Herston’. For the column_num argument it uses a combination of IF, COLUMN and MIN.

Here it is again for reference:

=INDEX(B1:F1,,MIN(IF(B2:F5=A9,COLUMN(A:E))))

In English the above formula reads:

Check the cells in the range B2:F5 for 'Herston' and tell INDEX what column number it's in. i.e. column 4. INDEX (look in) the range B1:F1 and return a reference to the 4th cell i.e. E1, which contains 4006.

So what’s MIN got to do with it….hold your horses, more on that in a moment.

Let’s step through the formula in the order it evaluates:

**Step 1 - IF function’s logical_test:** B2:F5=A9 i.e. B2:F5=Herston and it looks like this:

`=INDEX(B1:F1,,MIN(IF({FALSE,FALSE,FALSE,FALSE,FALSE; FALSE,FALSE,FALSE,FALSE,FALSE; FALSE,FALSE,FALSE,TRUE,FALSE; FALSE,FALSE,FALSE,FALSE,FALSE},,COLUMN(A:E))))`

*Tip**: did you notice in the formula above there is a semicolon after every 5 ^{th} ‘FALSE’ instead of a comma. This semicolon represents a new row in the array.*

Or if you imagine our formula is putting together a list of values representing each row in the table like this:

**Step 2 - COLUMN function:** This evaluates to return a horizontal array of numbers {1,2,3,4,5} for our IF function’s value_if_true argument. These numbers represent the 5 columns B:F in our table.

Our formula now looks like this:

=INDEX(B1:F1,,MIN(IF({FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,TRUE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE},{1,2,3,4,5})))

*Tip:** Instead of using the COLUMN function to generate the array of numbers we could simply type {1,2,3,4,5} into the formula. However, with large horizontal arrays it’s quicker (and dynamic) if we use the COLUMN function to generate the array, or for vertical arrays you can use the ROW function.*

*Note**: if you don’t want your COLUMN function to be dynamic you can use the INDIRECT function to fix it, like this: *

=INDEX(B1:F1,,MIN(IF(B2:F5=A9,COLUMN(INDIRECT("A:E")))))

**Step 3 - IF function value_if_true:** The IF function finishes evaluating by assigning the value_if_true numbers (generated by the COLUMN function) to the TRUE results in the logical_test.

To visualise this we can look at the 3^{rd} horizontal array (i.e. the series of FALSE/TRUE after the second semicolon below). Remember this is the 3^{rd} row of our table above.

Excel gives the TRUE results the corresponding number from the array generated from the COLUMN function {1,2,3,4,5} like so:

Note: In this step the FALSE values evaluate to nothing i.e. they are ignored. Remember we don’t have a value_if_false argument in our IF formula. Our formula now looks like this:

**Step 4 – MIN:** This simply evaluates to find the one and only number; 4.

`=INDEX(B1:F1,, 4)`

*Tip:** Since there is only one number remaining (the rest are all FALSE) we could have used MAX or SUM to get the same result as MIN. *

**Step 5 – INDEX:** Finally INDEX can return a reference to the 4^{th} column in the range B1:F1 which is cell E1 containing post code 4006.

*Tip:** Notice how our INDEX formula doesn’t have a row_num argument:*

*Since our reference is only one row high we don’t have to type a 1 in for the row_num argument, we simply enter a comma as a placeholder and continue on to the column_num argument.*

## What The?

Did you find that tricky?

When working with long or complex formulas I like to use the Evaluate Formula tool to understand what’s going on behind the scenes.

You can also evaluate parts of your formula by highlighting the section of the function in the formula bar and pressing the F9 key. Below I’ve evaluated the COLUMN(A:E) part of my formula:

To revert to the original formula either press the escape key or CTRL+Z.

## Thanks

Thanks to Mike for inspiring this post.

If you liked this please share it with your friends and colleagues.

Simply use the icons below to share it on Google +1, LinkedIn, Facebook and Twitter, or leave me a comment and tell me what formula would you use to find the Post Code?

Gabriela Lozano says

Very helpful. Do you think it’s possible to use the same concept to find the first column with a value in a range dinamically?

Mynda Treacy says

Hi Gabriela,

Do you mean like this: http://www.myonlinetraininghub.com/return-the-first-and-last-values-in-a-range

Mynda

Marshall Gordon says

I have the following table as a sample: A B C D E F G 1 Copper90 Copper75 Copper90 85 2 100 95 110 105 3 90 87 100 97 4 80 78 90 88 I hope to have a formula in G1 that will take the value in E1 and look for which column contains that value in row 1 range (A1:B1). Then, I thought the match function with match type -1 look for the smallest value in that column (?2:?4) that is = or > than value in F1. Finally, I would like to offset the matching cell by 2 columns to the right and copy and paste this value. In this example G1 = 100. If E1 was Copper75, G1 would be 97. I have tried several formulas with index, lookup, match functions, but have been unsuccessful. I can reconfigure the table if it makes the result I am looking for possible. Any advice would be greatly appreciated.

Mynda Treacy says

Hi Marshall,

Can you please send me your file via the help desk as the formatting of your data didn’t come through very well in your comment.

Thanks,

Mynda.

Krishna R M says

Its Simply Superb…!!! I’m in love with your website…!!<3

Cheers…!!:)

Mynda Treacy says

Aw, thanks Krishna 🙂 Glad you love it.

Michael Rempel says

Mynda, I’m struggling with what should be the simplest part of this formula – COLUMN. Why is it that when I break out that part of the formula and just type {=COLUMN(A:E)}, it returns a value of 1 rather than 1,2,3,4,5?

Mynda Treacy says

Hi Michael,

When you enter =COLUMN(A:E) in a single cell it can only return the first result, which is 1.

If you were to first higlight 5 cells (across a row) and then type in =COLUMN(A:E) and enter it as an array formula with CSE you will get a 1 in the first cell, a 2 in the second cell, a 3 in the third cell and so on.

This is a multi-cell array.

I hope that helps.

Kind regards,

Mynda.

Michael Rempel says

Thanks, Mynda. Little by little I’m getting it through my thick skull!

Mynda Treacy says

You’re welcome 🙂

pmsocho says

Very clever formula 🙂

Thanks!

Mynda Treacy says

Thank you, Piotr 🙂

Piotr says

I’m in love with SUMPRODUCT recently and I rebuild your cool formula so it doesn’t require CSE 🙂

=INDEX(B1:F1,,SUMPRODUCT((B2:F5=A9)*COLUMN(A:E)))

Mynda Treacy says

Love it, Piotr. Thanks for sharing 🙂

Pradeep says

This is Amaaaaazzzzzzing…. U r a Star Mynda…

I really love the way you explained it…. thanks

Mynda Treacy says

Wow, thank you, Pradeep 🙂