nested if function within specified time frame

[kobiashi]

Hi


can i use a nested if function with in a specific time fra,e or is there antoher function i could use.

i have a sheet, which a sample is attached to this thread,

i need to be able to search a two week period, with five conditions

so it would be

IF "V at Fault" column is populated and "Sub System", search two week period for exact match

IF "V at Fault" column is not populated, then use columns "V1,V2,V3" and "Sub System", search two week period for exact match (only one of the columns from "V1,V2,V3" with "sub System" needs to match

[JohnTopley]

"Sub System" is blank ... and what are we matching on?

[kobiashi]

Apologies


there will be a list in the "System" column and a sub list in the "Sub System" column, so it will be based on what is in the "Sub System" cell


here is the System list


list name "systems"

Air
ATO
Auxiliries
Bogies
Brakes
Carbody
Couplers
Doors
RS External
Traction
Transmission


and here is the first sub sysem list


List name "Air"

Air Leak
Compressor failure
Compressor Not Running
Excessive Compressor Operation
Low Air Pressure Indications
Suitable Code Not Found
Unusual Compressor Noise

[JohnTopley]

Please add these to your file AND add sample expected output.

[kobiashi]

hi thank you for your reply


i have added the lists to the sample sheet and added the desired output, which is column "Failure Level"

so normally if there is just one event, then the "Failure level" will be 1, if the there is two events for the criteria then the "failure level" will be 2, if there is three events then the "failure level" wil be 3, f there is four events then the "failure level" wil be 4 etc, up to level 5

i have used rows 4, 8, 12 and 16 as the example if what the output would look like

[JeteMc]

Try the following formula, pasted into cell R2 and copied down, which yields the same values as those previously placed in column R:

Please Login or Register  to view this content.

Let us know if you have any questions.