search odd rows only in given cell range

[gtd526]

Hello,
Im using the following formula and wondering how to search odd rows only in the given range (H4:H33)?

LOOKUP(2, 1/((COUNTIF($AI$116:$AI116,$H$4:$H$33)=0)*($AW$4:$AW$33<=$AW$1)),$H$4:$H$33)

Criteria = ($AW$4:$AW$33<=$AW$1), can be all rows
Starting Point when filling down ($AI$116:$AI116)

Thank you

 

[Coyotex3]

Maybe you can try adding this If statement to check whether the row is odd.

=IF(ISODD(ROW(H4:H33))=FALSE,"Even","Odd")

 

[gtd526]

Maybe you can try adding this If statement to check whether the row is odd.

=IF(ISODD(ROW(H4:H33))=FALSE,"Even","Odd")

Everything comes back as "Even".

NBA.xlsm
	AI	AJ
117	Even
118	Even
119	Even
120	Even
Favs

Cell Formulas
Range	Formula
AI117:AI120	AI117	=IF(ISODD(ROW($H$4:$H$33))=FALSE,"Even",LOOKUP(2,1/((COUNTIF($AI$116:$AI116,$H$4:$H$33)=0)*($AW$4:$AW$33<=$AW$1)),$H$4:$H$33))

 

[Peter_SSs]

Does this do what you want?
I would be wary about using the odd row numbers directly like this though. It is not uncommon to add a row into a worksheet and then suddenly those odd rows would become even rows.

=LOOKUP(2,1/((COUNTIF($AI$116:$AI116,$H$4:$H$33)=0)*($AW$4:$AW$33<=$AW$1)/ISODD(ROW(H$4:H$33))),$H$4:$H$33)

If the above formula does what you want and you would want to allow for possible row insertion (or deletion) then post back for a 'security update'

 

[gtd526]

Does this do what you want?
I would be wary about using the odd row numbers directly like this though. It is not uncommon to add a row into a worksheet and then suddenly those odd rows would become even rows.

=LOOKUP(2,1/((COUNTIF($AI$116:$AI116,$H$4:$H$33)=0)*($AW$4:$AW$33<=$AW$1)/ISODD(ROW(H$4:H$33))),$H$4:$H$33)

If the above formula does what you want and you would want to allow for possible row insertion (or deletion) then post back for a 'security update'

Does exactly what I'm looking for...Thank you!

 

[Peter_SSs]

Cheers. Thanks for the confirmation.

 

[gtd526]

Does this do what you want?
I would be wary about using the odd row numbers directly like this though. It is not uncommon to add a row into a worksheet and then suddenly those odd rows would become even rows.

=LOOKUP(2,1/((COUNTIF($AI$116:$AI116,$H$4:$H$33)=0)*($AW$4:$AW$33<=$AW$1)/ISODD(ROW(H$4:H$33))),$H$4:$H$33)

If the above formula does what you want and you would want to allow for possible row insertion (or deletion) then post back for a 'security update'

If I want to add 2 criteria do I add ISODD(Row, I've tried but its not working?
LOOKUP(2,1/((COUNTIF($U$116:$U116,$H$4:$H$33)=0)*($AS$4:$AS$33>=0.7)*($AP$4:$AP$33>=10)*($AW$4:$AW$33<=$AW$1)/ISODD(ROW(H$4:H$33))),$H$4:$H$33)

 

[gtd526]

If I want to add 2 criteria do I add ISODD(Row, I've tried but its not working?
LOOKUP(2,1/((COUNTIF($U$116:$U116,$H$4:$H$33)=0)*($AS$4:$AS$33>=0.7)*($AP$4:$AP$33>=10)*($AW$4:$AW$33<=$AW$1)/ISODD(ROW(H$4:H$33))),$H$4:$H$33)

Nevermind for now. Im testing it, and it doesn't seem to matter if I add ISODD to the criteria. Its only looking at ODD rows for the Answer.?.

 

[Peter_SSs]

That formula works for me. Yes, it is still only retrieving values from odd rows.

 

[gtd526]

That formula works for me. Yes, it is still only retrieving values from odd rows.

Thank you.
I'm looking to count the answer in the ODD rows. How would that been done?
Im currently using:
IF($U117="","",COUNTIF($H$4:$H$33,$U117))
but it count in all rows. i only want to count in ODD rows.
(U117 is the answer)