In my profession, we only service a certain area and I'm trying to create a
spreadsheet that will tell us if the area is within our service range.
Basically, the sheet must determine that when a Zip Code is entered into a
cell (C3), if the zip code is found, the County and City populate in two
cells below it.

I have a list of counties in cells F1:M1, and a list of cities in cells
F2:M2. Also, under each of these columns, I have between 7-12 zip codes
listed. (Lets say for the first two list of cells F3:F8, ,and G3:G11).

I would like my staff to enter a Zip code into cell C3. How can I write a
function that if the zip code is found between F3:F8, the county/city names
cells F1 and F2 populate in C4 and C5 OR if the zip is between G3:G11, the
names of cells G1 and G2 populate in the same C4 and C5 cells?

OR, if it would make it any easier, I could remove a row of cells,(F2:M2) so
that I would only need the data in F1:M1 to populate in cell C4.

I hope that wasn't confusing enough for anybody :( sorry! can anybody help?

In C4, enter
=IF(COUNTIF($F$3:$H$14,C3) 0,INDEX(1:1,SUMPRODUCT(($F$3:$M$14=C3)*COLUMN($F$ 3:$M$14))),"Out of
Area")
In C5, enter
=IF(COUNTIF($F$3:$H$14,C3) 0,INDEX(2:2,SUMPRODUCT(($F$3:$M$14=C3)*COLUMN($F$ 3:$M$14))),"Out of
Area")

HTH,
Bernie
MS Excel MVP


"offdah3z" wrote in message
...
In my profession, we only service a certain area and I'm trying to create a
spreadsheet that will tell us if the area is within our service range.
Basically, the sheet must determine that when a Zip Code is entered into a
cell (C3), if the zip code is found, the County and City populate in two
cells below it.

I have a list of counties in cells F1:M1, and a list of cities in cells
F2:M2. Also, under each of these columns, I have between 7-12 zip codes
listed. (Lets say for the first two list of cells F3:F8, ,and G3:G11).

I would like my staff to enter a Zip code into cell C3. How can I write a
function that if the zip code is found between F3:F8, the county/city names
cells F1 and F2 populate in C4 and C5 OR if the zip is between G3:G11, the
names of cells G1 and G2 populate in the same C4 and C5 cells?

OR, if it would make it any easier, I could remove a row of cells,(F2:M2) so
that I would only need the data in F1:M1 to populate in cell C4.

I hope that wasn't confusing enough for anybody :( sorry! can anybody help?

I forgot to mention that I assumed that the zip codes will be unique.

--
HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
In C4, enter
=IF(COUNTIF($F$3:$H$14,C3) 0,INDEX(1:1,SUMPRODUCT(($F$3:$M$14=C3)*COLUMN($F$ 3:$M$14))),"Out of
Area")
In C5, enter
=IF(COUNTIF($F$3:$H$14,C3) 0,INDEX(2:2,SUMPRODUCT(($F$3:$M$14=C3)*COLUMN($F$ 3:$M$14))),"Out of
Area")

HTH,
Bernie
MS Excel MVP


"offdah3z" wrote in message
...
In my profession, we only service a certain area and I'm trying to create a
spreadsheet that will tell us if the area is within our service range.
Basically, the sheet must determine that when a Zip Code is entered into a
cell (C3), if the zip code is found, the County and City populate in two
cells below it.

I have a list of counties in cells F1:M1, and a list of cities in cells
F2:M2. Also, under each of these columns, I have between 7-12 zip codes
listed. (Lets say for the first two list of cells F3:F8, ,and G3:G11).

I would like my staff to enter a Zip code into cell C3. How can I write a
function that if the zip code is found between F3:F8, the county/city names
cells F1 and F2 populate in C4 and C5 OR if the zip is between G3:G11, the
names of cells G1 and G2 populate in the same C4 and C5 cells?

OR, if it would make it any easier, I could remove a row of cells,(F2:M2) so
that I would only need the data in F1:M1 to populate in cell C4.

I hope that wasn't confusing enough for anybody :( sorry! can anybody help?