1

I have two sheets in an Excel document that I'm trying to compare. I have a list of area codes in Sheet 1 column B. In Sheet 2 I have a list of all area codes in columns A through D with the headers "Eastern, Central, Mountain, Pacific" in the the first row of each column followed by all area codes in each zone.

What I want to do is classify each area code in Sheet 1 in column C by the corresponding time zone in Sheet 2.

How do I do this?

Here are screen shots of the basic tables I have.

Sheet 1:
sheet 1

Sheet 2:
Sheet 2

  • Some diagrams of your data would help us understand your question. Please edit your question to include some example (mock) data. See Format Text as a Table for a web utility that will help you to create a nice data table you can paste into your question. – DavidPostill Aug 19 '16 at 22:20
  • I've searched every formula on the web for it, but the ones that I found are from 2006 and did not work. @raystafarian – jimmydoesitright Aug 22 '16 at 15:07
1

Here's how to do this with your data laid out exactly as you have it now. This formula goes in C2 on your Sheet1:

=IF(ISNA(MATCH(B2,Sheet2!$A$1:$A$40,0)),IF(ISNA(MATCH(B2,Sheet2!$B$1:$B$40,0)),IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific"),"Mountain"),"Central"),"Eastern")

It's ugly but it works. You may need to adjust the four absolute references to Sheet2 (e.g. Sheet2!$A$1:$A$40) if your data on that sheet goes past row 40.

The final formula consists of four uses of the MATCH function, each searching only one column of area codes.

Here's how I built the formula.

I used the MATCH function. MATCH can only search a single column for a value. First I created four separate MATCH formulas. Each searches only one of the timezone columns. For example, to search the "Eastern" column for the value (area code) found in cell B2:

=MATCH(B2,Sheet2!$A$1:$A$40,0)

Supposing B2 contains 201, this returns 2 because 201 is the 2nd item in the column. But if B2 contains 205 this returns #N/A. We catch this by wrapping the formula with the ISNA formula:

=IF(ISNA(MATCH(B2,Sheet2!$A$1:$A$40,0)),"no_next_formula","Eastern")

ISNA tests whether the MATCH formula returns #N/A. If yes/true, it returns "no_next_formula". If no/false it returns "Eastern" because MATCH found our area code in the Eastern area code column.

We can create four of these formulas, changing the range referenced on Sheet2 appropriately to search the other three area codes. An example worksheet might look like this:

enter image description here

Now, to combine these into the single formula shown above, I copied the entire formula in my column F (less the = sign) and replaced the text "no_next_formula" in column E's formula.

Formula in F2:

=IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific")

Formula in E2 (before):

=IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),"no_next_formula","Mountain")

Formula in E2 (after inserting formula from F2):

=IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific"),"Mountain")

I then repeated these steps, copying the formula now in E2 to replace the text "no_next_formula" in D2's formula. Once this is done for the formula in C2 we have the complete formula shown above.

1

If you’re sure that your data are well-formed (specifically, that no area code appears more than once in Sheet 2), then you can use

=SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2))

to find the column number (in Sheet 2) where the area code (in B2) appears.  (Replace 99 with the number of the last row in Sheet 2 where you have area codes.)  This is an array formula; you must type Ctrl+Shift+Enter after typing (or pasting) it.  This creates a virtual array, where each cell contains the column number multiplied by the truth value of whether the cell’s value is equal to B2, like this:

1*(Sheet2!A2=Sheet1!B2)   2*(Sheet2!B2=Sheet1!B2)   3*(Sheet2!C2=Sheet1!B2)   4*(Sheet2!D2=Sheet1!B2)
1*(Sheet2!A3=Sheet1!B2)   2*(Sheet2!B3=Sheet1!B2)   3*(Sheet2!C3=Sheet1!B2)   4*(Sheet2!D3=Sheet1!B2)
1*(Sheet2!A4=Sheet1!B2)   2*(Sheet2!B4=Sheet1!B2)   3*(Sheet2!C4=Sheet1!B2)   4*(Sheet2!D4=Sheet1!B2)
            ︙                        ︙                        ︙                        ︙

So, for example, if Sheet1!B2 is 303, this becomes

1*(201=303)               2*(205=303)               3*(208=303)               4*(206=303)
1*(202=303)               2*(210=303)               3*(303=303)               4*(209=303)
1*(203=303)               2*(214=303)               3*(307=303)               4*(213=303)
      ︙                        ︙                        ︙                        ︙

i.e.,

1*FALSE                   2*FALSE                   3*FALSE                   4*FALSE
1*FALSE                   2*FALSE                   3*TRUE                    4*FALSE
1*FALSE                   2*FALSE                   3*FALSE                   4*FALSE
   ︙                        ︙                        ︙                        ︙

Since TRUE is 1 and FALSE is 0, this reduces to

   0                         0                         0                         0
   0                         0                     →   3   ←                     0
   0                         0                         0                         0
︙   ︙                        ︙                        ︙                        ︙

and, of course, the SUM of that is 3, which is the number of the column where 303 is found.

If you’re sure that every area code in Sheet 1, Column B is present in Sheet 2, then you can get the name of the time zone simply by plugging the column number into an INDEX function:

=INDEX(Sheet2!A$1:D$1, 1, SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2)))

(remember to use Ctrl+Shift+Enter) but if you have an area code in Sheet 1 that isn’t present in Sheet 2, then Sheet2!whatever=B2 is FALSE for every cell, so the virtual array is all zeroes, the SUM is 0, and the INDEX may return something misleading.  To handle that, set Z2 to

=SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2))

(remember to use Ctrl+Shift+Enter) and set C2 to

=IF(Z2=0, "Not Found", INDEX(Sheet2!A$1:D$1, 1, Z2))
  • An elegant answer! Can you explain a bit more of how the formula in Z2 detects cases where an area code is not found on Sheet2? Also, in the formula following the paragraph If you’re sure that every area code in Sheet 1, Column B...do you mean to reference Sheet2 instead of Sheet5? – Twisty Impersonator Aug 24 '16 at 19:06
  • @Twisty: Thanks. And, yes, I meant to reference Sheet2 instead of Sheet5. (D’oh!) – Scott Aug 25 '16 at 3:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.