• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Loop without using Macros

Hi All,

I have a requirement where I need to compare the value in one cell [A1] with each and every cell in column B [B1,B2,B3...]. The number of rows in column B is ever changing.
If it is static , I can achieve it using the formula = if(or(A1=B1,A1=B2,A1=B3),"True","False").

How I can achieve a for loop [ to traverse all the cells in column B] without the use of macros and do it only using formulas.
 
Hi All,

I have a requirement where I need to compare the value in one cell [A1] with each and every cell in column B [B1,B2,B3...]. The number of rows in column B is ever changing.
If it is static , I can achieve it using the formula = if(or(A1=B1,A1=B2,A1=B3),"True","False").

How I can achieve a for loop [ to traverse all the cells in column B] without the use of macros and do it only using formulas.
Hi,

You can do this


=IF(COUNTIF(B:B,A1),TRUE,FALSE)
 
Hi,

Say the value to compare is in A1 and value to compared with are in column B starting from B1. Than you can use array formula like below:

=IF(OR(--(A1=B1:B6)),TRUE,FALSE)

I had values in B1:B6. Confirm this formula with Ctrl+SHift+Enter.

or alternatively you can also use normal formula like:

=IF(COUNTIF(B1:B6,A1),TRUE,FALSE)

Enter only with Enter.

Or one more method with normal Enter:

=IF(SUMPRODUCT(--(A1=B1:B6)),TRUE,FALSE)

One more thing You need not to put TRUE & FALSE in "".

Regards,
 
HI Mishra,

Formulas with hyphen are not working.

=IF(COUNTIF(B1:B6,A1),TRUE,FALSE)

=IF(SUMPRODUCT(--(A1=B1:B6)),TRUE,FALSE)

I am getting an error Value!, can you help
 
Back
Top