I have two sheets:

  1. Contains form number and amount
  2. Contains bill number and amount

Form Table

I tried to reconcile which bill belongs to which form using simple lookup based on the amount, ignoring duplicate amount and using the first value found.

Bill Table

After verification, this results in a static (no formula) reconciliation table, which will be updated as more form-bill get reconciled.

Reconciliation Table

I created a column to check whether a form number has been reconciled.

Form Table with flag column

Now, I want to update the bill table's lookup to exclude reconciled entries, so I put a condition to only lines entries with "#N/A".

Updated Lookup

But all of it returns N/A error!

How to fix this? I want to lookup for entries with matching amount AND has #N/A value.


Turn unsearchable NA to searchable string

The easiest one is to use IFNA to wrap the reconcile check column and turns it into "None" string.


Then use "None" as your filter.


This should also works for all other errors, just replace IFNA to IFERROR.

  • I arrived at this answer just after I submitted the question, and wow, it only requires simple solution. – Vylix Oct 6 '18 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.