Look up table for Area codes

[catharsis50]

I am trying to clean up lists for contact uploads. When there is a blank state field I want to be able to look up the first three numbers of the phone number and run it against a table of area codes and states I have to get the state quickly. My issue may be with the way my look up table is set up, but I feel like this should be doable.

I have all the states in column a, and all area codes within that state listed next to it from column B:to however many area codes that state has. I have been able to be successful getting it to return Alabama for numbers starting with 205 but nothing else when testing on this single row example.

Alabama 205 251 256 334 938 Phone 3349986687

Your help is apreciated, I can email the file to you if that helps.

Thanks!

 

[T. Valko]

I am trying to clean up lists for contact uploads. When there is a blank state field I want to be able to look up the first three numbers of the phone number and run it against a table of area codes and states I have to get the state quickly. My issue may be with the way my look up table is set up, but I feel like this should be doable.

I have all the states in column a, and all area codes within that state listed next to it from column B:to however many area codes that state has. I have been able to be successful getting it to return Alabama for numbers starting with 205 but nothing else when testing on this single row example.

Alabama 205 251 256 334 938 Phone 3349986687

Your help is apreciated, I can email the file to you if that helps.

Thanks!

Do any area codes exist in more than 1 state?

 

[catharsis50]

Yes they do, I figured I would tackle that after getting the lookup to function properly first.

 

[T. Valko]

Yes they do, I figured I would tackle that after getting the lookup to function properly first.

Try something like this...

Book1
	A	B	C	D	E	F
2	Alabama	205	251	256	334	938
3	Alaska	123	456		_	_
4	Arkansas	502	666	777	_	_
5	_	_	_	_	_	_
6	3349986687	Alabama	_	_	_	_
Sheet1

This array formula** entered in B6:

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

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[catharsis50]

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 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.

 

[catharsis50]

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.

 

[T. Valko]

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

 

[catharsis50]

Wow, thanks for taking the time to explain that. Greatly appreciated.

 

[T. Valko]

Wow, thanks for taking the time to explain that. Greatly appreciated.

You're welcome. Thanks for the feedback!