# Find Value in Another Sheet

#### ULast

##### New Member
We have a list of names in column A in Sheet 1. We would like to return a "Yes" in column B for every row for which column A has a name that matches ANY name found in the entire sheet of Sheet 2. Please advise.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Guru008

##### Board Regular
Hi,

Try this =IF(MATCH(H3,A2:A20,0)>0,"Yes","")

I am able to respond in real time to you until it is solved!

#### My Aswer Is This

##### Well-known Member
Try this:

Code:
``````Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
For Each c In Sheets(2).UsedRange
If c.Value = Sheets(1).Cells(i, 1).Value Then Sheets(1).Cells(i, 2).Value = "Yes"
Next
Next
Application.ScreenUpdating = True
End Sub``````

OK

#### ULast

##### New Member
Try this:

Code:
``````Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
For Each c In Sheets(2).UsedRange
If c.Value = Sheets(1).Cells(i, 1).Value Then Sheets(1).Cells(i, 2).Value = "Yes"
Next
Next
Application.ScreenUpdating = True
End Sub``````

OK

Thanks for the reply. I'm not sure how to use this code. Can you please provide some insight?

#### hiker95

##### Well-known Member
I'm not sure how to use this code. Can you please provide some insight?

ULast,

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
``````Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim c As Range
Dim Lastrow As Long
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
For Each c In Sheets(2).UsedRange
If c.Value = Sheets(1).Cells(i, 1).Value Then Sheets(1).Cells(i, 2).Value = "Yes"
Next
Next
Application.ScreenUpdating = True
End Sub``````

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the Test macro.

Replies
0
Views
606
Replies
4
Views
560
Replies
8
Views
378
Replies
7
Views
2K
Replies
12
Views
596

1,191,165
Messages
5,985,040
Members
439,935
Latest member
Monty238

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