Broken array formula

Question

I have updated a spreadsheet I use an array formula, I added extra columnsto my sheet and the formula stopped working.  Now the data range is still in place and on the updated sheet the formula just does not want to work.  I have uploaded the files to my onedrive (please use link below) original is V0 and the tab's SCAR Outstanding the tab WCAR Outstanding have the original array formula.  On V1 there is the updated and broken formula on tab SCAR & WCAR Outstanding.

Many thanks

Adam

http://1drv.ms/1ybrrdK

Answer 1

Hi,

I would create another worksheet with a 2 column table like below and do a vlookup on that

=iferror(vlookup(V2,mytable,2,false),"")

008	Customer Servive
009	Planning
010	Eating
etc	Drinking

Answer 2

Hi,

A couple of things.

1. The array formula isn't broken, it's the data that are a mess returning #N/A for that gargantuan  formula in AP of the worksheet warranty cars. I enclosed it in IFERROR to get rid of the errors.
2. Column O changed as above.
3. The array formula is over the top, a simple countifs will do it. This would do the same as H3

=COUNTIFS('Warranty CAR''s'!$AR$2:$AR$800,A500,'Warranty CAR''s'!$AP$2:$AP$800,E3)

Lots of these formula now return zero because the criteria isn't satisfied. I uploaded a working version to my onedrive

Car Report Database V1.xlsx

Answer 3

Mike,

        Many thanks for the help.  The reason for the gargantuan  formula in AP of the worksheet warranty cars is to auto populate AP when a corrosponding code has been input into cell V. 

I wonder if you have a simplier method of that formula what I really wanted but culdnt get my head around how to do is that if you input a code between the range of 008 to 011 then that populates AP with the corrosponding department.  But this needs to be done for 10 different sets of numbers that could be entered in V.

Adam.