 Highlighted

# If formual

HI

I need to set up an if function to compare text results in my spreadsheet eg. if cell 80 has a P and cell 79 has a b than the result should be 1 , if Cell 80 has a P and cell 79 has  a P then result should be -1

can someone help with this

3 Replies
Highlighted

# Re: If formual

@donny475 Try something like this:

``=IFERROR(IFS(AND(A79="b",A80="p"),1,AND(A79="p",A80="p"),-1),"")``

Note: the search strings "b" and "p" are not case-sensitive.

Highlighted

# Re: If formual

Thanks for that it worked but I also with in the same formulae need the reverse ie if a80 =b and a79 =P 1

and if A80 =b and a79 =b -1

Highlighted

# Re: If formual

Just add the extra conditions like this:

``=IFERROR(IFS(AND(A79="b",A80="p"),1,AND(A79="p",A80="p"),-1,AND(A79="p",A80="b"),1,AND(A79="b",A80="b"),-1),"")``

Or, if you intend to add a loot more combinations, consider using a lookup table. Then, the formula will become much shorter and easier to maintain. See the workbook attached for an example.