Lookup search how to ignore blank cells ?

[sebasjuh84]

Goodmorning,

I have a cell created in Excel to search on another tab if the word is recognized and if so that I see the value that stands with it.
I have done that with the following formula;

Sheets("Januari").Range("D" & Regelnummerbegin).Value = "=LOOKUP(2^15,SEARCH('#Gegevens'!$A$2:$A$" & Gegevenscount & ",F" & Regelnummerbegin & "),'#Gegevens'!$B$2:$B$" & Gegevenscount & ")"


The problem I now have is that the value 'Gegevenscount' is count with the actual fields that are filled.
But when I afterwards set more values to check they are ignored because the formula in the cell don't check those records.

So i tried the following;

Sheets("Januari").Range("D" & Regelnummerbegin).Value = "=LOOKUP(2^15,SEARCH('#Gegevens'!$A$2:$A$65000,F" & Regelnummerbegin & "),'#Gegevens'!$B$2:$B$65000)"

Only then all the values are 0 as result because there are a few filled in cells but the most cells are empty and it's not working good.
So can someone help me on how to edit the formula so that I can use the last formula only that it is ignoring the empty cells ?

Thanks!

[Kaper]

If the data is added top->bottom (with no empty rows in between - just empty rows below data) you can try to include COUNTA in your formula, for instance:

Please Login or Register  to view this content.