I have an excel file with 10,000 rows in column A some values are the same.

Example:

```
A1 - P7767
A2 - P3443
A3 - P7767
A4 - P8746
A5 - P9435
```

etc...

I then have another column with 100 rows which have some of the values found in column A,

```
B1 - P7767
B2 - P8746
```

etc...

I need to highlight all cells in column A where the value is found in any of the values in column B

So basically column B checks to see if it can find the same value anywhere in column A, if true highlight the cell leaving any cells white when the value is not found in column B

I hope I have explained this well, I have done some research and I believe I need to use conditional formatting to get this result but I am really stuck on the formula to use and cannot seem to find an example online (Maybe I am not searching the correct term as I'm not sure on what this is exactly called)

This question is related to
`excel`

`excel-formula`

There may be a simpler option, but you can use VLOOKUP to check if a value appears in a list (and VLOOKUP is a powerful formula to get to grips with anyway).

So for A1, you can set a conditional format using the following formula:

```
=NOT(ISNA(VLOOKUP(A1,$B:$B,1,FALSE)))
```

Copy and Paste Special > Formats to copy that conditional format to the other cells in column A.

What the above formula is doing:

- VLOOKUP is looking up the value of Cell A1 (first parameter) against the whole of column B ($B:$B), in the first column (that's the 3rd parameter, redundant here, but typically VLOOKUP looks up a table rather than a column). The last parameter, FALSE, specifies that the match must be exact rather than just the closest match.
- VLOOKUP will return #ISNA if no match is found, so the NOT(ISNA(...)) returns true for all cells which have a match in column B.

- Python: Pandas pd.read_excel giving ImportError: Install xlrd >= 0.9.0 for Excel support
- Converting unix time into date-time via excel
- How to increment a letter N times per iteration and store in an array?
- 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)
- How to import an Excel file into SQL Server?
- Copy filtered data to another sheet using VBA
- Better way to find last used row
- Could pandas use column as index?
- Check if a value is in an array or not with Excel VBA
- How to sort dates from Oldest to Newest in Excel?
- Creating an Array from a Range in VBA
- Excel: macro to export worksheet as CSV file without leaving my current Excel sheet
- VBA: Convert Text to Number
- EPPlus - Read Excel Table
- How to label scatterplot points by name?
- What's the difference between "end" and "exit sub" in VBA?
- Rename Excel Sheet with VBA Macro
- Extract Data from PDF and Add to Worksheet
- Quicker way to get all unique values of a column in VBA?
- Multiple conditions in an IF statement in Excel VBA
- How to find and replace with regex in excel
- Unprotect workbook without password
- Excel is not updating cells, options > formula > workbook calculation set to automatic
- Find row number of matching value
- If "0" then leave the cell blank
- Clear contents and formatting of an Excel cell with a single command
- Remove Duplicates from range of cells in excel vba
- Delete worksheet in Excel using VBA
- Get list of Excel files in a folder using VBA
- Excel doesn't update value unless I hit Enter
- Declare a variable as Decimal
- Parse XLSX with Node and create json
- Detect if a Form Control option button is selected in VBA
- Get length of array?
- Object of class stdClass could not be converted to string - laravel
- Java - Writing strings to a CSV file
- Quickest way to clear all sheet contents VBA
- VBA: Counting rows in a table (list object)
- Excel VBA If cell.Value =... then
- VBA Excel - Insert row below with same format including borders and frames
- excel - if cell is not blank, then do IF statement
- filter out multiple criteria using excel vba
- Referencing value in a closed Excel workbook using INDIRECT?
- Use Excel VBA to click on a button in Internet Explorer, when the button has no "name" associated
- IndexError: too many indices for array
- File name without extension name VBA
- (Excel) Conditional Formatting based on Adjacent Cell Value
- Easy way to export multiple data.frame to multiple Excel worksheets
- Using ExcelDataReader to read Excel data starting from a particular cell
- What are the RGB codes for the Conditional Formatting 'Styles' in Excel?
- [Move to Excel compare two columns and highlight duplicates]

- Excel doesn't update value unless I hit Enter
- Referencing value in a closed Excel workbook using INDIRECT?
- Conditionally formatting cells if their value equals any value of another column
- Sum values from multiple rows using vlookup or index/match functions
- What does an exclamation mark before a cell reference mean?
- If two cells match, return value from third
- Excel - programm cells to change colour based on another cell
- Format numbers in thousands (K) in Excel
- Excel Formula which places date/time in cell when data is entered in another cell in the same row
- Excel formula to display ONLY month and year?
- Check if an excel cell exists on another worksheet in a column - and return the contents of a different column
- Conditionally formatting if multiple cells are blank (no numerics throughout spreadsheet )
- How can I combine multiple nested Substitute functions in Excel?
- Excel formula to search if all cells in a range read "True", if not, then show "False"
- Add a "sort" to a =QUERY statement in Google Spreadsheets
- Sum up a column from a specific row down
- Using "If cell contains #N/A" as a formula condition.
- Excel formula to get week number in month (having Monday)
- Excel select a value from a cell having row number calculated
- Showing percentages above bars on Excel column graph
- Excel: Can I create a Conditional Formula based on the Color of a Cell?
- How to SUM parts of a column which have same text value in different column in the same row
- Excel formula is only showing the formula rather than the value within the cell in Office 2010
- A formula to copy the values from a formula to another column
- How to enter a formula into a cell using VBA?
- Excel: last character/string match in a string
- Check whether a cell contains a substring
- Excel how to find values in 1 column exist in the range of values in another
- How can I sort one set of data to match another set of data in Excel?
- How to compare two columns in Excel and if match, then copy the cell next to it
- If Cell Starts with Text String... Formula
- How can I color dots in a xy scatterplot according to column value?
- How to remove only 0 (Zero) values from column in excel 2010
- How to Convert Excel Numeric Cell Value into Words
- In Excel how to get the left 5 characters of each cell in a specified column and put them into a new column
- In excel how do I reference the current row but a specific column?
- Converting time stamps in excel to dates
- Refer to a cell in another worksheet by referencing the current worksheet's name?
- Combining COUNT IF AND VLOOK UP EXCEL
- Set formula to a range of cells
- Excel- compare two cell from different sheet, if true copy value from other cell
- Excel Formula: Count cells where value is date
- Excel: Searching for multiple terms in a cell
- Excel - find cell with same value in another worksheet and enter the value to the left of it
- In Excel, sum all values in one column in each row where another column is a specific value
- Ignore Duplicates and Create New List of Unique Values in Excel
- Pass row number as variable in excel sheet
- PivotTable to show values, not sum of values
- Using the value in a cell as a cell reference in a formula?
- Return Max Value of range that is determined by an Index & Match lookup
- [Move to Excel compare two columns and highlight duplicates]