IFERROR() returning previous with VLOOKUP instead of designated value

NateDIl wrote:
=SUM(IFERROR(VLOOKUP(A2, TABLE1 :: $A$2:$B$31,2),0),IFERROR(VLOOKUP(A2, TABLE2 :: $A$2:$B$31,2),0),IFERROR(VLOOKUP(A2, TABLE3 :: $A$2:$B$31,2),0)... hopefully you get the picture.

Hi Nate,

I'm getting apparently correct results with this formula:

=SUM(IFERROR(VLOOKUP(A2, Table 1 :: $A$2:$B$31,2),0),IFERROR(VLOOKUP(A2, Table 2 :: $A$2:$B$31,2),0),IFERROR(VLOOKUP(A2, Table 3 :: $A$2:$B$31,2),0),IFERROR(VLOOKUP(A2, Table 4 :: $A$2:$B$31,2),0))

("Apparently correct" as I haven't checked the individual results, but have included a checksum formula which returns zero if the sum of the counts equals the number of entried on tables 1 through 4.)

Getting "the previous lookup value" can occur when the current lookup value is greater than any value in the first column of the lookup table.

eg: On my model of your tables, I used A, B, C, D, and E as the possible index values. If I included F in the Search for values on Table 5, the formula returned the count of the Es on tables 1 through 4. That's expected behavior for VLOOKUP, as you'll find in the description of that function in the iWork formulas and Functions User Guide.

If you haven'yt yet downloaded the guide (and the equally useful Numbers '09 User Guide), I'd suggest doing so. You find the download links listed as items in the Help menu in numbers.

Regards,
Barry

This may or may not be your problem but there is a fourth parameter to VLOOKUP that lets you specify close match or exact match. The default when you leave it off is close match. In other words, it will find the closest match. For instance if I search for "f" in a list that contains a,b,c,d it will return the value for "d". You say you are searching for repeats which to me means you want exact matches. With it finding close matches, you may never get an error (unless the thing being searched for is very different from the list being searched) so the IFERROR parts of the formula won't come into play.