This works great, I appreciate it.

I get that the first part is indexing the state names, I don't understand how the max function is being used or what the end row(B2)+1 does.

You must be busy;if you have the time can you explain how this is working. I was attempting to make this work with an index match combo with no luck.

Again thanks, this is a big help.

The INDEX function "holds" the values of the referenced range in a specific order.

If the indexed range is A2:A4 then:

A2 is in position 1

A3 is in position 2

A4 is in position 3

To get the result we want we need to tell the INDEX function to return the value from position N.

In the example I posted I'm using:

IF(B2:F4=--LEFT(A6,3),ROW(B2:F4))

To return the ROW number of the cell that matches the area code. The row number is the absolute row number. In the example the row number is 2. This number is passed to the INDEX function and tells it we want the result at indexed positon 2.

However, the value at position 2 is not the correct result we want. This is because we have to use an "offset correction" to change the absolute row number so that it aligns with the positons numbers used by INDEX.

We use:

-ROW(B2)+1

As the offset correction.

In the example:

IF(B2:F4=--LEFT(A6,3),ROW(B2:F4))

Returns the following array which is then passed to the MAX function:

{FALSE,FALSE,FALSE,

**2**,FALSE;FALSE,FALSE,FALSE,

FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE}

MAX({FALSE,FALSE,FALSE,2,FALSE;FALSE,FALSE,FALSE,

FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE})

=2

Then, we apply the offset correction:

2-ROW(B2)+1

=2-2+1

=1

Which is passed to INDEX:

=INDEX(A2:A4,1)

Return the value at position 1 of the indexed range:

=Alabama

So:

=INDEX(A2:A4,MAX(IF(B2:F4=--LEFT(A6,3),ROW(B2:F4)))-ROW(B2)+1)

=Alabama