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

#### Costtx

##### New Member
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.

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
44.8 KB · Views: 1
• 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.

##### Well-known Member
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
92SaraN/A
101ClarkN/A
111.1HaidiN/A
121.2JackN/A
132SusanN/A
141LeonardN/A
15
Sheet1
Cell Formulas
RangeFormula

#### Costtx

##### New Member

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.

#### Michael M

##### Well-known Member
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
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``````

##### Well-known Member

Try this:
Book1
ABC
1Test StepStep/Action
21Launch the Web Application URLN/A
42Logging in as a [@User_Role = Learner] ***** ****** **** ********** * ********* *** ********** * **************N/A
64From the menu, click USERS > User Management.N/A
75Click Create UserN/A
86Enter the User details.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

#### Costtx

##### New Member
Try this:
Book1
ABC
1Test StepStep/Action
21Launch the Web Application URLN/A
42Logging in as a [@User_Role = Learner] ***** ****** **** ********** * ********* *** ********** * **************N/A
64From the menu, click USERS > User Management.N/A
75Click Create UserN/A
86Enter the User details.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
Perfect! Thank you again.

##### Well-known Member
You're Welcome & Thanks for Feedback.

Replies
6
Views
161
Replies
0
Views
268
Replies
0
Views
57
Replies
6
Views
197
Replies
0
Views
95

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.

### 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