Prev: Macro Help - Jacob Skaria has previously been helping
Next: create a separate list for unique records
From: Rechie on 28 Apr 2010 05:25
Hi Experts,
How can I get the results of vlookup where there are two corrsponding data
in other sheet. One is blank and the other is non blank. I want to get the
results of non blank cell and ignore the blank one.

Thanks

From: Jacob Skaria on 28 Apr 2010 05:34
Hi Rechie

The below formula would lookat Sheet2 ColA for the lookup value and return
the non blank entry from Col B.

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula>}"

=INDEX(Sheet2!$B$1:$B$100,MATCH(1,(Sheet2!$A$1:$A$100=lookup_value)*
(Sheet2!$B$1:$B$100<>""),0))

--
Jacob (MVP - Excel)


"Rechie" wrote:

> Hi Experts,
> How can I get the results of vlookup where there are two corrsponding data
> in other sheet. One is blank and the other is non blank. I want to get the
> results of non blank cell and ignore the blank one.
>
> Thanks
>
 | 
Pages: 1
Prev: Macro Help - Jacob Skaria has previously been helping
Next: create a separate list for unique records