Yes there is a way to lookup on composite criteria (subdivision+plan), but it requires an extra field. I've done it.
I'm just copying one cell from a spreadsheet I made so long ago, I'll probably miss some details:
(I ommited all the code which takes care of 'what if no match' to keep it simple here)
I'm looking in "table2" for a match on two cells c39 and d39 (your subdivision and plan).
Table2 lookup value is a composite of two items made by concatenation.
So if your two items are SubdivisionA (in C39) and PlanA (inD39), the composite would be unreadable SubdivisionAPlanA you'd put in the first column of what I named table2 above, but Excel doesn't mind if you can't read it
Regarding tables on a different sheet - just qualify the names with sheet name in front of a specific location or a named reference. The syntax is SheetName!cell (or named range)
Regarding what VLOOKUP() finds or doesn't, from Excel Help:
If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value.
For EXACT lookup, use the range parameter = FALSE
Hope this helps a bit.
Edited by tos226, 04 April 2006 - 11:13 PM.