IFs, Index/Match and Check Boxes

Your IF formula only tells your sheet what to do if Closed@row = 0. You forgot to add in the false value. Just before the final ) put a comma and then tell it where to get the value from if Closed@row does not equal 0.

I'm not sure that will work. I want the data in the assigned to column to stay as is, do nothing when or if the box is checked.

We're going to need a screenshot of at least one and maybe both sheets (without sensitive data). It sounds like you're putting the formula in one sheet, referring to the Assigned To in another sheet but only if Closed = 0. Because the location of your formula is blank without an index match to fill it out, the result of course will be blank. What else could it be?

Here is sheet 1:

Here is Sheet 2

When the region is populated on Sheet 1 - there is an Index Match that looks up region on Sheet 2 and returns the Name to the Regional Champion (assigned to) column on Sheet 1. I only want this to happen if the Closed check box is not checked.

If the Closed check box is checked, I want the current regional champion to remain as is.

I can see where you're getting your Regional Champion from if Closed = 0. It also looks like the formula you're working on is in the Regional Champion column on Sheet 1, correct?

So where on either sheet is the Regional Champion supposed to be grabbed from when Closed = 1? It can't be the Regional Champion column on sheet 1 because that is a formula and hence blank if not index matching when Closed = 0.

If it's a static person (meaning always a certain person if Closed = 1), then you can tell it what to put in the Regional Champion column by something like this:

=IF(Closed@row = 0, INDEX({Region}, MATCH(Region@row, {Name}), 0), "Paul Jones")

Not a static person. that is part of my dilemma.

The regional person could change (so details on sheet 2 change), but we don't want them to change on sheet 1 if the closed is checked.

So I'm not sure if a helper column on sheet 2 that get's locked down. I know if it is on sheet one it creates a circular reference

Ok, but where is it supposed to be grabbed from when Closed = 1? There's no way to help if you don't provide that information.

I think I figured this out. If I had an Original Champion column in sheet 2, then if the box is checked I can have it pull that data.

Thanks for your patience and support.

Exactly. You have to point it somewhere. I guess you were trying to point it to a location that you hadn't created yet lol.