Formula based on data in 2 column

Chandresh

Board Regular
Joined
Jul 21, 2009
Messages
132
HI ALL,

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

IDType
A500S
A500S
A501P
A501P
A506P
A506P
A506S
A506S
A506S
A506S

<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
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 21, 2009
Messages
132
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.

IDType
A500SOK
A500SOK
A501POKoutput should be Check
A501POKoutput should be Check
A501SOKoutput should be Check
A506PCHECK
A506PCHECK
A506SCHECK
A506SCHECK
A506SCHECK
A506SCHECK

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

Thanks
chandresh
 

Finalfight40

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows
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.

IDType
A500SOK
A500SOK
A501POKoutput should be Check
A501POKoutput should be Check
A501SOKoutput should be Check
A506PCHECK
A506PCHECK
A506SCHECK
A506SCHECK
A506SCHECK
A506SCHECK

<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")

(If you have already adjusted to your range, then you can just add the = sign in the 2 places in red.)
 

nikio8

Board Regular
Joined
Oct 20, 2017
Messages
128

ADVERTISEMENT

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
0 not found
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top