How to write an IF function for picking matching cells with a particular value?

**NEEDiiHELP** · 07-19-2017, 08:00 AM

I need to compare two columns so that I get a TRUE value only if values in L (from row 3 down) equals values in S and both are equal to one, [but NOT if L3 is not equal to S3 OR if they are equal but contain a value other than One]

eg: i need

L S BU
1 1 YES
2 1 NO
2 2 NO
1 2 NO

i tried =IF(L3=S3=1,"YES","NO") but this answered all cells with 'NO'

i also tried all variations of that i could think of, as well as trying 'and', =IF((L3=1, S3=1),"YES","NO"), putting 1 in quotes like =IF(L3=S3="1") etc, but i can't seem to get this to work, please please help!!!

**Special-K** · 07-19-2017, 08:04 AM

You were close in your second formula

=IF(AND(L3=1,S3=1),"YES","NO")

"putting 1 in quotes"
A value should only be in quotes if it is text. Since these are numbers then you simply need =1 not ="1"

**NEEDiiHELP** · 07-19-2017, 08:12 AM

OMG OMG THAT WORKED!!! Thank you so so very much!!!

**Special-K** · 07-19-2017, 08:50 AM

AND() OR() formulas in Excel work a little different to other languages you may have come across.

You have to specify all the conditions that need to be true for the AND() OR() formula.

So AND(L3=1,S3=1) simply means if L3=1 AND S3=1, same format applies to OR conditions OR(L3=1,S3=1) would mean if L3=1 OR S3=1

**NEEDiiHELP** · 07-19-2017, 03:01 PM

I see, so I can extend the same formula for more data in the same way! And the OR will come in handy too, thank you so much!!!

How to write an IF function for picking matching cells with a particular value?

LinkBack

Thread Tools

Rate This Thread

Display

How to write an IF function for picking matching cells with a particular value?

Re: How to write an IF function for picking matching cells with a particular value?

Re: How to write an IF function for picking matching cells with a particular value?

Re: How to write an IF function for picking matching cells with a particular value?

Re: How to write an IF function for picking matching cells with a particular value?

Thread Information

Users Browsing this Thread

Similar Threads

Macro to write custom function into cells

[SOLVED] SMALL() Picking Empty Cells In An Array Function

Picking up correct data after matching up columns using vlookup with match

Matching 2 cells and picking value from a 3rd cell that is based on repeated text field

[SOLVED] How do i write to cells in a function?

how to write if function to assign values to multiple cells not only one

write a function to determine if cells have names

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions