formula help (excel2003)

Topic

New Reply

hi all,
I need a formula that will flaq “planned” in coulmn d sheet1 if there is a match between name&numb in sheet2 with name&numb in sheet1 and not planned flaq only if date value in sheet1 is 2007? attached book has a sample of the data and the expected results.
regards,

Reply | Quote

Replies

Formula for D2 is

=IF(YEAR(C2)=2007,IF(A2 & B2=Sheet2!A2 & Sheet2!B2,"planned","not planned"),"")

Reply | Quote

many thanks SammyB. what would be the array formula that will give the result in the attached book.

dubdub

Reply | Quote

Please explain why you expect something different than SammyB’s formula yields.

BTW, two of the dates on Sheet1 are not valid – they contain a double slash //.

Reply | Quote

hi HansV,
i have included an explination for the expected results.
regards,
dubdub

Reply | Quote

In row 2 on Sheet1, name = “sma” and numb = 1. This combination occurs on Sheet2 in row 2, and the year is 2007.
In row 3 on Sheet1, name = “hana” and numb = 2. This combination occurs on Sheet2 in row 8, and the year is 2007.

Why should the first one be “planned” and the second one “not planned”?

PS Your workbook contains 3 empty modules. You shouldn’t include modules in an attachment unless they are essential for the question.

Reply | Quote

hansv,
both should be planned as i showed in the attached file expected results column, but applying SammyB’s formula it assumes the data in both sheets have the same order, which is not.
see what i am after is a two criteria condition, one matching sheet 2 name& numb with name&numb in sheet 1 if match exist then it will be “planned”, next for all other name and numb in sheet 1 which are not “planned” from the previous step (one) but they are 2007 i want them to be “not planned”.
regards,

Reply | Quote

Oops, sorry, I switched the expected result with that of the formula. Try this array formula in D2 (confirm with Ctrl+Shift+Enter):

=IF(YEAR(C2)=2007,IF(ISERROR(MATCH(A2&B2,Sheet2!$A$2:$A$9&Sheet2!$B$2:$B$9,0)),”not planned”,”planned”),””)

You can fill down this formula.

Reply | Quote

Many thanks hansV.

dubdub

Reply | Quote