Some formulas break when Lookup data is moved?

I tried changing the whole column references in both LOOKUP calls in U:AA.

That did the trick.

if cells AC1:AE10 are moved down a row (shift-delete / cntrl-v) the formulas (in U3:AA10) break. I am really puzzled why only those cells break and the rest (the cells below those in u:aa) don't... actually, I'm puzzled why they break in the first place.

I can't articulate well why that happens. But you are trying to move a section (AC1:AE10) of a whole range AC:AE. AC:AE has to accommodate that with no place to "move". It's out of rows.

Hope that helps.

Hi CWatson,

Try this formula in AE2 instead of your CSE formula,

=LOOKUP(2,1/(A:A=AC2),ROW(A:A))

Perhaps the problem occurs when you try to edit a single formula in a range of CSE formulas?

Let us know what happens with the above formula in AE2.

Hi MarvinP - I will use your formula for AE2 (actually AE3 and down since I moved that whole section down a row) but it still breaks the section for the A-10 conference.

While defining the rows for the conference names works (in ac:ae) and I will change my formulas in u:aa to reflect that, it still peaks my curiosity as to why just the first section (for the A-10 conference) doesn't work when the rest of the rows work fine.

It can't have anything to do with moving the formulas in ac:ae down a row because when I start fresh, with ac:ae starting in row 3, the A-10 conference still doesn't work. It has to be something with the lookup formula. When I ran the lookup all by itself, it still didn't work.

If I put =LOOKUP($A5,$AC:$AD) in AF5 and =LOOKUP($A5,$AC:$AE) in AG5, I get errors. If I copy and paste those formulas down, I get errors until AF11 and AG11 (where the AAC conference starts) and then it works just fine.

Okay, now it sounds like I am just rambling.

Thank you for your help - I don't know if I should mark this solved or not...

Hey Cliff,

If you select the cell with the formula and then click on Formulas and Evaluate, you can step through the parts of the formula. It fails at the Lookup() part. I believe you should put row numbers on your AC:AE as these are entire columns.

https://support.office.com/en-us/art...6-a70aa409b8a7

When I get these types of errors, it is because I have a dollar sign in my formula that is wrong. When I move a formula down it misses a fixed range because I need an absolute range and not a relative one.

You also have an Indirect() in here which complicates the formula, only wanting to look at column I, but the Lookup() is what evaluates incorrectly first.

I am in the process of changing the formulas in u:aa (ac:ai) to absolute references (I have over 5k rows to copy down once I get the first row done)

If you are not aware of it Excel has a Find and replace feature. That's what I used. Just select/highlight the range of U:AA down to the last row with formula.

Then use Find and replace (Ctrl + H) and fill in the text boxes. Click find and replace all.

That saves a ton of work.

Hope that helps.