Mix Vlookup with conditional formatting in excel to get awesome results

Get the file here: s3.amazonaws.com/a2excel.com/...
In this video we will see how to illuminate data that is found in an Excel list, but was not found in another list. As an example, we have a table of transactions, and we will use conditional formatting and vlookup to locate the suppliers that were not in the table of authorized suppliers
.
This is a complicated issue that requires prior knowledge of vlookup, it is for people who know intermediate Excel or advanced Excel. We will also use the if function and the ifna function in Excel. By itself, none of these functions of Excel is complicated, but the necessary combination to work if it is. At the end, we will apply our functions in the conditional formatting where it asks us Use a formula that determines the cells to apply format.
We start by assembling an additional column in our table where we will mark if the provider is valid or not valid. For this, our first step will be to set up a search to determine if the provider was found on the authorized list or not. If the search is successful, it will bring the name of the provider, otherwise it will bring an error of type # N/A in Excel.
To deal with this error, we can use Excel's ifna function to give us an alternative result, such as "not valid" for example. However, we continue with the problem that the search provider gives us.
Therefore, we wrap everything in a function if, where the value if true brings us "not valid" and the value if false brings us "valid". The tricky thing is to twist the true and false to bring us the indicator we want.
Now we will repeat the same in our conditional format. We are going to use the tool Use a formula that determines the cells to apply format, in which we are asked for a formula that returns true or false to know whether to paint or not.
In this conditional format, we write our search. Then we use the ifna in the conditional formatting, and we end with a conditional that returns 1 (true) and 0 (false). This is enough for Excel.
To finish, we illuminate the entire table supporting us with absolute cell references.

[@mamallama9711]

Thank you!!!!! You totally saved me with this video. Exactly what I needed for a work project. This was very clear and easy to understand.