# Formula based on data in 2 column

#### Chandresh

##### Board Regular
HI ALL,

Could you please help me with formula which can give me result based on data from 2 different column.

 ID Type A500 S A500 S A501 P A501 P A506 P A506 P A506 S A506 S A506 S A506 S

<tbody>
</tbody>

I have data in 2 columns A and B, I need a formula which can lookup the data in entire column and provide me the results.

- if data against ID is "S" then "OK"
- if data against ID is "P. then "OK"
- if the data against ID is "S" and "P" then "Check" - (ID A506 has both P and S)

Thanks
Chandresh

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Finalfight40

##### Active Member
Would this work? You would need to adjust the ranges to match your data but:

=IF(SUM(IF(COUNTIFS(A1:A10, A12, B1:B10, "S")>1, 1, 0)+IF(COUNTIFS(A1:A10, A12, B1:B10, "P")>1, 1, 0))=2, "CHECK", "OK")

Where:
A1:A10 is the range of ID
B1:B10 is the range of Type
A12 is the ID you are looking up

#### Chandresh

##### Board Regular
Would this work? You would need to adjust the ranges to match your data but:

=IF(SUM(IF(COUNTIFS(A1:A10, A12, B1:B10, "S")>1, 1, 0)+IF(COUNTIFS(A1:A10, A12, B1:B10, "P")>1, 1, 0))=2, "CHECK", "OK")

Where:
A1:A10 is the range of ID
B1:B10 is the range of Type
A12 is the ID you are looking up

Thanks for your reply , I have applied the formula in my working sheet however its working for some cases. I have added one more id "A501" in the data base now the output comes as "OK".
Ideally it should come as "Check" as there are 2"P" and 1 "S", could you please help.

 ID Type A500 S OK A500 S OK A501 P OK output should be Check A501 P OK output should be Check A501 S OK output should be Check A506 P CHECK A506 P CHECK A506 S CHECK A506 S CHECK A506 S CHECK A506 S CHECK

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Thanks
chandresh

#### Finalfight40

##### Active Member
Thanks for your reply , I have applied the formula in my working sheet however its working for some cases. I have added one more id "A501" in the data base now the output comes as "OK".
Ideally it should come as "Check" as there are 2"P" and 1 "S", could you please help.

 ID Type A500 S OK A500 S OK A501 P OK output should be Check A501 P OK output should be Check A501 S OK output should be Check A506 P CHECK A506 P CHECK A506 S CHECK A506 S CHECK A506 S CHECK A506 S CHECK

<tbody>
</tbody>

Thanks
chandresh

Ah my bad i think i just missed off the = sign where the > signs are:

The corrected formula below:

=IF(SUM(IF(COUNTIFS(A1:A10, A12, B1:B10, "S")>=1, 1, 0)+IF(COUNTIFS(A1:A10, A12, B1:B10, "P")>=1, 1, 0))=2, "CHECK", "OK")

#### nikio8

##### Board Regular

are you sure that rows are correct? in row 2
=IF(COUNTIFS(A:A, A2, B:B, "S")>1, 1, 0)+IF(COUNTIFS(A:A, A2, B:B, "P")>1, 1, 0)
1 ok
2 check

#### Chandresh

##### Board Regular
Thanks for your help its working fine now

You are welcome

#### jtakw

##### Well-known Member
Hi,

A little simpler:

Book1
ABC
1IDType
2A500SOK
3A500SOK
4A501PCHECK
5A501PCHECK
6A501SCHECK
7A506PCHECK
8A506PCHECK
9A506SCHECK
10A506SCHECK
11A506SCHECK
12A506SCHECK
Sheet161
Cell Formulas
RangeFormula
C2=IF(AND(COUNTIFS(A\$2:A\$12,A2,B\$2:B\$12,"S"),COUNTIFS(A\$2:A\$12,A2,B\$2:B\$12,"P")),"CHECK","OK")

Formula copied down.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,803
Messages
5,855,748
Members
431,760
Latest member
IceAg08

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back