Vlookup with corresponding two data (blank and non blnk)

[Rechie]

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

 

[Jacob Skaria]

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))