conditional formula

[Dummy Excel]

Hi All,
im trying to lookup a few conditions (registration and status of vehicle from one worksheet) and then display comments in another worksheet. The main issue is i cant use vlookups as the registration in the reverse sheet is there multiple times which is why i need to use the status column (unavailable) to get my unique comment

my formula so far is:

=IF(COUNTIFS(Reverse!B:B,C73,Reverse!AG:AG,"Unavailable"),Reverse!AP:AP,"test")

although it displays random data, i cant work out where its getting the data from as its completely wrong. Even with the "test" there!

can anyone help?

thanks
Sam

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

 

[Dummy Excel]

summary sheet

[RANGE=cls:xl2bb-100][XR][XH=cs:4]Book1[/XH][/XR][XR][XH][/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][/XR][XR][XH]2[/XH][XD=h:l|fw:b]Rego[/XD][XD=h:c]Status (OSR)[/XD][XD=h:l]Initial Repair Comments (OSR)[/XD][/XR][XR][XH]3[/XH][XD=h:l|fw:b]Banana[/XD][XD=h:c]Unavailable[/XD][XD=h:l|cls:fx][FORMULA==IF(COUNTIFS(Reverse!B:B,C3,Reverse!AG:AG,"Unavailable"),Reverse!AP:AP,"test")]rego expires 02/05/18[/FORMULA][/XD][/XR][XR][XH=cs:4][RANGE][XR][XD]South[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]E3[/XD][XD]=IF(COUNTIFS(Reverse!B:B,C3,Reverse!AG:AG,"Unavailable"),Reverse!AP:AP,"test")[/XD][/XR][/RANGE]

reverse sheet

[RANGE=cls:xl2bb-100][XR][XH=cs:8]Book1[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]AG[/XH][XH]AP[/XH][/XR][XR][XH]1[/XH][XD=h:l|cls:fx][FORMULA=Row Count]Row Count[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Rego Number]Rego Number[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Reference #]Reference #[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Status]Status[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Action Status]Action Status[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Final Status]Final Status[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Breakdown Issue (Specific Details Only)]Breakdown Issue (Specific Details Only)[/FORMULA][/XD][/XR][XR][XH]2[/XH][XD=h:r|cls:fx][FORMULA=443]443[/FORMULA][/XD][XD=h:l|fw:b|cls:fx][FORMULA=Banana]Banana[/FORMULA][/XD][XD=h:r|cls:fx][FORMULA=11312435]11312435[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Complete]Complete[/FORMULA][/XD][XD=h:r][/XD][XD=h:l|bc:fff2cc|cls:fx][FORMULA=Available]Available[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Rego check tom 16 at Miranda]Rego check tom 16 at Miranda[/FORMULA][/XD][/XR][XR][XH]3[/XH][XD=h:r|cls:fx][FORMULA=416]416[/FORMULA][/XD][XD=h:l|fw:b|cls:fx][FORMULA=Banana]Banana[/FORMULA][/XD][XD=h:r|cls:fx][FORMULA=11302984]11302984[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Complete]Complete[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Confirmed (Available, safe to use, although repairs to be completed)]Confirmed (Available, safe to use,
although repairs to be completed)[/FORMULA][/XD][XD=h:l|bc:fff2cc|cls:fx][FORMULA=Unavailable]Unavailable[/FORMULA][/XD][XD=h:l|cls:fx][FORMULA=Cradle not charging the laptop]Cradle not charging the laptop[/FORMULA][/XD][/XR][XR][XH=cs:8][RANGE][XR][XD]Reverse[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2|h:l|fw:b]Cell Formulas[/XH][/XR][XR][XH]Cell[/XH][XH]Formula[/XH][/XR][XR][XD]A1[/XD][XD]Row Count[/XD][/XR][XR][XD]A2[/XD][XD]443[/XD][/XR][XR][XD]A3[/XD][XD]416[/XD][/XR][XR][XD]B1[/XD][XD]Rego Number[/XD][/XR][XR][XD]B2[/XD][XD]Banana[/XD][/XR][XR][XD]B3[/XD][XD]Banana[/XD][/XR][XR][XD]C1[/XD][XD]Reference #[/XD][/XR][XR][XD]C2[/XD][XD]11312435[/XD][/XR][XR][XD]C3[/XD][XD]11302984[/XD][/XR][XR][XD]D1[/XD][XD]Status[/XD][/XR][XR][XD]D2[/XD][XD]Complete[/XD][/XR][XR][XD]D3[/XD][XD]Complete[/XD][/XR][XR][XD]E1[/XD][XD]Action Status[/XD][/XR][XR][XD]E3[/XD][XD]Confirmed (Available, safe to use, although repairs to be completed)[/XD][/XR][XR][XD]F1[/XD][XD]Date Work Commenced[/XD][/XR][XR][XD]F3[/XD][XD]43171[/XD][/XR][XR][XD]G1[/XD][XD]Estimated date when repairs will be completed[/XD][/XR][XR][XD]G3[/XD][XD]43175[/XD][/XR][XR][XD]H1[/XD][XD]Date when repairs were completed[/XD][/XR][XR][XD]I1[/XD][XD]Comments[/XD][/XR][XR][XD]I3[/XD][XD]Parts[/XD][/XR][XR][XD]J1[/XD][XD]Comments Status[/XD][/XR][XR][XD]J3[/XD][XD]Parts[/XD][/XR][XR][XD]K1[/XD][XD]Issue is with[/XD][/XR][XR][XD]K2[/XD][XD]Vehicle (Truck or Ute)[/XD][/XR][XR][XD]K3[/XD][XD]Vehicle (Truck or Ute)[/XD][/XR][XR][XD]L1[/XD][XD]Is the Vehicle Driveable?[/XD][/XR][XR][XD]L2[/XD][XD]Yes[/XD][/XR][XR][XD]L3[/XD][XD]Yes[/XD][/XR][XR][XD]M1[/XD][XD]Is the Plant & Equipment Operational?[/XD][/XR][XR][XD]N1[/XD][XD]When did the breakdown occur?[/XD][/XR][XR][XD]O1[/XD][XD]Status of the Vehicle/P&E[/XD][/XR][XR][XD]O2[/XD][XD]Available (Not Tagged Out)[/XD][/XR][XR][XD]O3[/XD][XD]Available (Not Tagged Out)[/XD][/XR][XR][XD]P1[/XD][XD]Final Status[/XD][/XR][XR][XD]P2[/XD][XD]Available[/XD][/XR][XR][XD]P3[/XD][XD]Unavailable[/XD][/XR][XR][XD]Q1[/XD][XD]Vehicle been towed to?[/XD][/XR][XR][XD]R1[/XD][XD]Depot Name[/XD][/XR][XR][XD]S1[/XD][XD]Repairer Name[/XD][/XR][XR][XD]T1[/XD][XD]Area you belong to[/XD][/XR][XR][XD]T2[/XD][XD]South[/XD][/XR][XR][XD]T3[/XD][XD]South[/XD][/XR][XR][XD]U1[/XD][XD](North) Local depot for Vehicle/P&E[/XD][/XR][XR][XD]V1[/XD][XD](South) Local depot for Vehicle/P&E[/XD][/XR][XR][XD]W1[/XD][XD](West) Local depot for Vehicle/P&E[/XD][/XR][XR][XD]X1[/XD][XD]North - Vehicle Type[/XD][/XR][XR][XD]Y1[/XD][XD]North - Plant & Equipment Type[/XD][/XR][XR][XD]Z1[/XD][XD]West - Vehicle Type[/XD][/XR][XR][XD]AA1[/XD][XD]West - Plant & Equipment Type[/XD][/XR][XR][XD]AB1[/XD][XD]South - Vehicle Type[/XD][/XR][XR][XD]AB2[/XD][XD]Truck/Ute - Rego Due[/XD][/XR][XR][XD]AB3[/XD][XD]Truck/Ute - Electrical Issue[/XD][/XR][XR][XD]AC1[/XD][XD]South - Plant & Equipment Type[/XD][/XR][XR][XD]AD1[/XD][XD]Rego of Excavator on Trailer?[/XD][/XR][XR][XD]AE1[/XD][XD]Excavator Comments[/XD][/XR][XR][XD]AF1[/XD][XD]Status for Excavator[/XD][/XR][XR][XD]AF2[/XD][XD]Available[/XD][/XR][XR][XD]AF3[/XD][XD]Available[/XD][/XR][XR][XD]AG1[/XD][XD]Final Status[/XD][/XR][XR][XD]AG2[/XD][XD]Available[/XD][/XR][XR][XD]AG3[/XD][XD]Unavailable[/XD][/XR][XR][XD]AH1[/XD][XD]Overdue Service related to[/XD][/XR][XR][XD]AI1[/XD][XD]Issue related to[/XD][/XR][XR][XD]AJ1[/XD][XD]Address of where vehicle broke down[/XD][/XR][XR][XD]AK1[/XD][XD]Tyre Size (e.g. 185/60R13) OR (e.g. 21.7x7.3R13)[/XD][/XR][XR][XD]AL1[/XD][XD]Please enter odometer or hours reading[/XD][/XR][XR][XD]AL2[/XD][XD]Odometer (KM)[/XD][/XR][XR][XD]AL3[/XD][XD]Odometer (KM)[/XD][/XR][XR][XD]AM1[/XD][XD]Odometer reading:[/XD][/XR][XR][XD]AM2[/XD][XD]8679[/XD][/XR][XR][XD]AM3[/XD][XD]8569[/XD][/XR][XR][XD]AN1[/XD][XD]Hours reading:[/XD][/XR][XR][XD]AO1[/XD][XD]Serial Number of Excavator[/XD][/XR][XR][XD]AP1[/XD][XD]Breakdown Issue (Specific Details Only)[/XD][/XR][XR][XD]AP2[/XD][XD]Rego check tom 16 at Miranda[/XD][/XR][XR][XD]AP3[/XD][XD]Cradle not charging the laptop[/XD][/XR][/RANGE]

 

[Dummy Excel]

can anyone help?