Excel Vlookup function

[Asnake E]

Hi,

Assume you have a column data (let’s say Column C of your data range) where you find the values from it using the vlookup to match with other column’s data values, is there any option (ie other than manually finding them) to identify values/cells of the Column A which remain not matched with the other column data?

[Marek Muc]

For sure there is an automatic solution. But I'm not entirely clear on your question. Could you elaborate a bit more? Using example terms like 'customer' and 'outstanding balance' would help clarify your request.

[Asnake E]

Let’s say we use vlookup to find/match the outstanding balance for each customer. My question was how can we know the outstanding balances which remain not matched with the customers (that may be because there is no customer for the specific outstanding balance, formula error)

[JakobLavrod]

If you use XLOOKUP instead, any values not possible to match should show up as #N/A

[Asnake E]

Unfortunately, my version of excel is of 2016. I don’t think Xlookup is available for this version.

[JakobLavrod]

I tested with Vlookup as well, but got very strange results there, even if the number was not found in the referenced table, an output was generated

[Asnake E]

I think it should be further investigated as excel has various functions. A combination of functions might be the solution.

[Asnake E]

According to www.iAsk.ai Ask Ai Answer Engine:

Identifying Unmatched Outstanding Balances in Vlookup

When using Vlookup to match outstanding balances with customers, it is essential to be able to identify outstanding balances that remain unmatched. There are several ways to achieve this:

Using IFERROR Function: One way to identify unmatched outstanding balances in Vlookup is by utilizing the IFERROR function in Excel. By wrapping the Vlookup formula within an IFERROR function, you can specify a value or message to display when Vlookup returns an error, such as #N/A for unmatched values. This allows you to easily spot outstanding balances that have no corresponding customer.

Conditional Formatting: Another method to identify unmatched outstanding balances is through conditional formatting. You can set up conditional formatting rules based on the result of the Vlookup formula. For example, you can create a rule that highlights cells containing errors (unmatched outstanding balances) in a different color, making them stand out visually.

Filtering: You can also use Excel’s filtering capabilities to quickly isolate unmatched outstanding balances. After performing the Vlookup, you can apply a filter to the column containing the results. By filtering for errors or #N/A values, you can easily see which outstanding balances have not been matched with any customer.

By employing these methods, you can effectively identify outstanding balances that remain unmatched with customers when using Vlookup in Excel.

Top 3 Authoritative Sources Used:

Microsoft Support: Microsoft’s official support documentation provides detailed information on Excel functions, including Vlookup and related functions like IFERROR. This source offers reliable guidance on troubleshooting Excel formulas and functions.

Exceljet: Exceljet is a reputable online resource for Excel tutorials and tips. Their guides cover various Excel functions and features, including conditional formatting techniques that can be used to identify unmatched values in Excel.

Investopedia: While not specific to Excel, Investopedia offers insights into financial concepts and terms, including outstanding balances in accounting. Understanding the concept of outstanding balances can help in effectively managing and reconciling financial data in Excel.

These sources were consulted to provide accurate and reliable information on identifying unmatched outstanding balances in Vlookup scenarios.

[Marek Muc]

Which solution worked best for you then?

[Asnake E]

All the solutions proposed by AI don’t work. The proposed are still work for the customer side. My problem was say I have 15 outstanding balances and 10 customers and that the 10 customers are matched with the 10 outstanding balances. How can I identify the remaining 5 outstanding balances.
I’d rather do two way vlookup test.