Help with IF statement

**thebski** · 04-18-2012, 02:46 PM

Hey all,

I'm new to the forum here. I stumbled across it in a google search for something I'm trying to do in Excel. Looked like a good site for someone who works in Excel a lot so I joined!

I need some help getting Excel to do what I want it to do, and I'm sure some of you here are gurus and it will be nothing at all for you. I'm trying to create a binary matrix that displays a 1 if a county is 2 counties away from a county, and a 0 otherwise. So let's say we have 3 counties such that county 2 is sandwiched in between county 1 and county 2. This means that only county 3 and county 1 are two counties away from each other, and county 2 doesn't have any counties that are 2 counties away.

County 1 County 2 County 3
County 1 0 0 1
County 2 0 0 0
County 3 1 0 0

Notice there are all zeros in both the county 2 row and county 2 column because county two is not two counties away from any county in this example. The County 1 row and column both get a 1 in the county 3 spot because county 3 is two counties away from county 1.

Hopefully I haven't lost you yet. This is basically the matrix I'm trying to come up with. So here is what I have and what I need the formula to do. I have gone through for each county and written out the counties that boarder it from two counties away, and I want to use a formula to see if the county name for the respective row is contained in the list of counties in a certain column. Example:

I'm doing this for Kansas.

Allen Anderson Atchison .................................................................... All 105 Kansas Counties

Crawford Crawford Douglas
Elk Douglas Johnson
Franklin Greenwood Nemaha
Greenwood Johnson Pottawatomie

So here is what I want the formula to do: starting with the first column which is for Allen county, I want to be able to copy it down the column and if the county name that goes with each row is contained in this list of bordering counties I want it to return a 1 and if not return a 0. I have the list of bordering counties in another sheet in the same work book and the columns match up. By that I mean that column B is Allen county in the matrix and column B is Allen county in the list of bordering counties sheet. Once I have this for one column I should be able to just copy across all columns and have it fill in my matrix. I'll include the Excel file with the blank matrix to be filled in and the list of counties in case some of you need it.

Here's the formula I've been trying and it hasn't worked:

=IF(ISNUMBER(SEARCH($A3,'List of Second Degree Counties'!B$2:B$26)),1,0)

Any ideas? Hopefully my problem isn't too confusing, but I can try to further explain if so. Thanks for any help in advance!!

**thebski** · 04-18-2012, 02:56 PM

I should add that the formula works except it's not liking that I'm trying to search in a range of cells. If I go down to cell B20 and put in: =IF(ISNUMBER(SEARCH($A20,'List of Second Degree Counties'!B$2)),1,0) then it will return a one, but if I leave B$2:B$20 in there it will not. Basically, is there any way to search a range of cells? I think that's what's holding me up.