IF function that searches a range of cells for data and provide tr


O

offdah3z

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?
 
Ad

Advertisements

B

Bernie Deitrick

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top