How to match on null cells

[Nick Danger]

I want to look up a value in one column of my worksheet in another
worksheet, using the MATCH() function. In some rows, this column is empty.
For this case, I want to use a default value, so I extended the range of the
lookup table by one row, assuming that the null cell being compared would
match the null cell in the new last line of the lookup range. Does anyone
know if there is a way to do this without resorting to IF() or replacing all
the null cells with some other placeholder value?

 

[Dave Peterson]

One way:

=INDEX(B1:B10,MATCH(TRUE,A1:A10="",0))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Dave Peterson]

Sorry.

The instructions should have been:

=INDEX(B1:B10,MATCH(TRUE,A1:A10="",0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.