M
Mark
Aim:To automatically have returned ONLY values
(irrespective of match or not!).
Problem: I need to total all values returned but some
lookup's return #N/A where no true/exact match, so being
non-value can't sum total.
Data matrix 100x100, so too big to manually correct each
occurence of #N/A and table_array is smaller than
lookup_value range so FALSE answer producing #N/A is
always a possibility.
So situation, using VLOOKUP as defined below 1) & 2);
1) VLOOKUP
(lookup_value,table_array,col_index_num,range_lookup)
2) If VLOOKUP can't find lookup_value, and range_lookup is
FALSE, VLOOKUP returns the #N/A value
The answer will sometimes be FALSE, hence #N/A returned in
cell.
Surely someone has come across this problem where they
need to total lookup values returned and have devised a
way to automatically input value (say 0.00) in leiu of
#N/A.
Any suggestions
thx
Mark
(irrespective of match or not!).
Problem: I need to total all values returned but some
lookup's return #N/A where no true/exact match, so being
non-value can't sum total.
Data matrix 100x100, so too big to manually correct each
occurence of #N/A and table_array is smaller than
lookup_value range so FALSE answer producing #N/A is
always a possibility.
So situation, using VLOOKUP as defined below 1) & 2);
1) VLOOKUP
(lookup_value,table_array,col_index_num,range_lookup)
2) If VLOOKUP can't find lookup_value, and range_lookup is
FALSE, VLOOKUP returns the #N/A value
The answer will sometimes be FALSE, hence #N/A returned in
cell.
Surely someone has come across this problem where they
need to total lookup values returned and have devised a
way to automatically input value (say 0.00) in leiu of
#N/A.
Any suggestions
thx
Mark