If A2 contains a value of 1.2 AND B2 contains a value of Learner

Costtx

New Member
Joined
May 15, 2017
Messages
19
Hi There,

I would like to know if there is an excel formula which can be used to search for specific values within a pair of cells.

1628498312523.png


As you can see from the attached picture, I have used the IF(ISNUMBER(SEARCH function to indicate which cells contain the words 'Learner' or 'Site Admin', otherwise it displays 'N/A'.

What I would like is if there were a function which searches column A to determine if it has a value of 1.2; if so I want to then search column B to determine if it has one of the two values specified.

In other words, If A4 = 1.2 and B4 contains Learner or Site Admin then I want C4 to display Learner or Site Admin.

Is this possible?

Thanks!
 

Attachments

  • 1628498071153.png
    1628498071153.png
    44.8 KB · Views: 1
  • 1628498306182.png
    1628498306182.png
    53.3 KB · Views: 1

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,675
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this at C2 and Drag it down:
Excel Formula:
=IF(A2=1.2,IF(RIGHT(B2,7)="Learner","Learner",IF(RIGHT(B2,10)="Site Admin","Site Admin","N/A")),"N/A")
Book1
ABCD
1StepNamePosition
21PeterN/A
31.1SamN/A
41.2LearnerLearner
52EmilyN/A
61MarkN/A
71.1JoeN/A
81.2Site AdminSite Admin
92SaraN/A
101ClarkN/A
111.1HaidiN/A
121.2JackN/A
132SusanN/A
141LeonardN/A
15
Sheet1
Cell Formulas
RangeFormula
C2:C14C2=IF(A2=1.2,IF(RIGHT(B2,7)="Learner","Learner",IF(RIGHT(B2,10)="Site Admin","Site Admin","N/A")),"N/A")
 

Costtx

New Member
Joined
May 15, 2017
Messages
19
Hi Maabadi,

Thank you for taking the time to respond to the query I loaded yesterday. I was wondering if you would be prepared to look at this a bit further for me?

What I provided was not the actual data, just something I was trying to get an answer from and try to apply to the actual worksheet. It turns out a bit more complex than that.

I'll provide you with some of the actual data.

If the Test Step Column contains 1.2 and if the Step/Action contains either Learner, Site Admin, Learning Admin, Manager, Content Curator, Content Coordinator or Learning Admin User, then that role should be reflected in the Role column.


RoleTest StepStep/Action
1Launch the Web Application URL
1.2Login as [@User_Role = Learner]
2Logging in as a [@User_Role = Learner] ***** ****** **** ********** * ********* *** ********** * **************
3Select Login
4From the menu, click USERS > User Management.
5Click Create User
6Enter the User details.
7Select 'Require this user to change their password during first-time log in'
10Select or enter values
11Click Create
1.2Login as [@User_Role = Learner]
***** ****** **** ********** * ********* *** ********** * **************
1.2Login as [@User_Role = Site Admin] ***** ****** **** ********** * ********* *** ********** * **************
1.2Login as [@User_Role = Learning Admin] ***** ****** **** ********** * ********* *** ********** * **************
1.2Login as [@User_Role = Manager] ***** ****** **** ********** * ********* *** ********** * **************
1.2Login as [@User_Role = Content Curator] ***** ****** **** ********** * ********* *** ********** * **************
1.2Login as [@User_Role = Content Coordinator] ***** ****** **** ********** * ********* *** ********** * **************
1.2Login as [@User_Role = Learning Admin User] ***** ****** **** ********** * ********* *** ********** * **************
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,101
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I get the feeling there may be more criteria to this but try a VBA
VBA Code:
Sub MM1()
 Dim  r As Long
 For r = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1
    If Cells(r, 2) = 1.2 And InStr(Cells(r, 3), "Site Admin") Then
        Cells(r, 4) = "Site Admin"
    End If
    If Cells(r, 2) = 1.2 And InStr(Cells(r, 3), "Learner") Then
         Cells(r, 4) = "Learner"
    End If
Next r
End Sub
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,675
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Try this:
Book1
ABC
1Test StepStep/Action
21Launch the Web Application URLN/A
31.2Login as [@User_Role = Learner]Learner
42Logging in as a [@User_Role = Learner] ***** ****** **** ********** * ********* *** ********** * **************N/A
53Select LoginN/A
64From the menu, click USERS > User Management.N/A
75Click Create UserN/A
86Enter the User details.N/A
97Select 'Require this user to change their password during first-time log in'N/A
1010Select or enter valuesN/A
1111Click CreateN/A
121.2Login as [@User_Role = Learner] ***** ****** **** ********** * ********* *** ********** * **************Learner
131.2Login as [@User_Role = Site Admin] ***** ****** **** ********** * ********* *** ********** * **************Site Admin
141.2Login as [@User_Role = Learning Admin] ***** ****** **** ********** * ********* *** ********** * **************N/A
151.2Login as [@User_Role = Manager] ***** ****** **** ********** * ********* *** ********** * **************N/A
161.2Login as [@User_Role = Content Curator] ***** ****** **** ********** * ********* *** ********** * **************N/A
171.2Login as [@User_Role = Content Coordinator] ***** ****** **** ********** * ********* *** ********** * **************N/A
181.2Login as [@User_Role = Learning Admin User] ***** ****** **** ********** * ********* *** ********** * **************N/A
Sheet1
Cell Formulas
RangeFormula
C2:C18C2=IF(A2=1.2,IF(MID(B2,FIND("=",B2)+2,7)="Learner","Learner",IF(MID(B2,FIND("=",B2)+2,10)="Site Admin","Site Admin","N/A")),"N/A")
 
Solution

Costtx

New Member
Joined
May 15, 2017
Messages
19
Try this:
Book1
ABC
1Test StepStep/Action
21Launch the Web Application URLN/A
31.2Login as [@User_Role = Learner]Learner
42Logging in as a [@User_Role = Learner] ***** ****** **** ********** * ********* *** ********** * **************N/A
53Select LoginN/A
64From the menu, click USERS > User Management.N/A
75Click Create UserN/A
86Enter the User details.N/A
97Select 'Require this user to change their password during first-time log in'N/A
1010Select or enter valuesN/A
1111Click CreateN/A
121.2Login as [@User_Role = Learner] ***** ****** **** ********** * ********* *** ********** * **************Learner
131.2Login as [@User_Role = Site Admin] ***** ****** **** ********** * ********* *** ********** * **************Site Admin
141.2Login as [@User_Role = Learning Admin] ***** ****** **** ********** * ********* *** ********** * **************N/A
151.2Login as [@User_Role = Manager] ***** ****** **** ********** * ********* *** ********** * **************N/A
161.2Login as [@User_Role = Content Curator] ***** ****** **** ********** * ********* *** ********** * **************N/A
171.2Login as [@User_Role = Content Coordinator] ***** ****** **** ********** * ********* *** ********** * **************N/A
181.2Login as [@User_Role = Learning Admin User] ***** ****** **** ********** * ********* *** ********** * **************N/A
Sheet1
Cell Formulas
RangeFormula
C2:C18C2=IF(A2=1.2,IF(MID(B2,FIND("=",B2)+2,7)="Learner","Learner",IF(MID(B2,FIND("=",B2)+2,10)="Site Admin","Site Admin","N/A")),"N/A")
Perfect! Thank you again.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,675
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for Feedback.
 

Forum statistics

Threads
1,147,474
Messages
5,741,341
Members
423,656
Latest member
Medrok2021

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