Spreadsheet Formula - Nested VLOOKUP & ISERROR

The first thing to know about spreadsheet formulas is that they are portable between applications. Yes, if you are familiar with MS Excel and have to use iWork Numbers because that is what the client wants (this was my dilemma) you can do your testing in Excel if you like. Then copy the formula over to your spreadsheet of choice (numbers, open office calc, Google docs spreadsheet, etc.) and it will work.

The Business Problem

I needed to search for an IP address of known hosts in inbound AND outbound traffic. If there was a positive result that meant the traffic was NOT captured in a firewall ACL and more log analysis was needed before a deny rule could be implemented.

The Word Problem

I need to search two columns for one value and if there is a match in either column return a positive result.

The Formula

After much trial and error, pinging my network of professional resources, countless cups of coffee I found the formula:

=IF(ISERROR(VLOOKUP([cell1],[value],2,FALSE)),IF(ISERROR(VLOOKUP([cell2],[value],2,FALSE)),"Not Legit",VLOOKUP([cell2],[value],2,FALSE)),VLOOKUP([cell1],[value],2,FALSE))

The ISERROR was the key to the nested VLOOKUPs and the key to closing out the loop was the last VLOOKUP. That took a while to understand (not totally sure why it worked, please comment if you are sure).

I hope this formula can save someone else time and caffeine.