Counting Your Hits - Excel array formula
VLOOKUP can only be used to search for hits in the first column of the range of cells you select. Today when searching for a dozen or so loan numbers that might be listed in any one of more than hundred columns in a spreadsheet with thousands of rows, I wanted an easy way to check and see if any one of them was listed anywhere the spreadsheet without CTRL + F for each one. This formula does the trick:
=SUM(IF(ISERROR(SEARCH(A1,$A$10:$H$12000)),,1))
[THIS IS AN ARRAY FORMLA - WHEN ENTERING PRESS CTRL + SHIFT + ENTER. Brackets should appear on either side.]
In the smaller example spreadsheet shown in the screen grab below we enter the formula in cell B1 to search for the first value in cell A1, and then pull it down with CTRL + D. We can quickly see which of the values we need to actually find in the spreadsheet. This formula may be most useful when you have a long list of search terms and you're fairly certainly that only a few of them appear in a very long spreadsheet that needs to be searched.
See this site for more information on the formula: