How can I skip blank cells when looking for the first row with a value?

[excelishardsometimes]

I am running an analysis and in some runs the first appearance of a sample will be blank and the second will have a value. When I use vlookup it returns blank. Is there another method that will return the 1st number?

Well ID	Name	Well	Conc/Dil	Lum	Mean	CV (%)	Normalization - Mean	CV (%)	% Inhibition	lookup	value
CTL3	sLPC1	C1	22​	101​	99​	2.9​	1.1379​	2.9​		sLPC1 % INH	0
		C2	22​	97​
CTL12	sLPC1	C7	22​	95​	95​	0​	1.092​	0​	4.0404​
		C8	22​	95​					4.0404​

this is the formula I am using:

=VLOOKUP("sLPC1",B:J,9,FALSE)

4.0404 is the value I need not 0.

Any ideas? Thanks!

 

[Anthony47]

You use Office 365, thus you may use Filter and Let. Try using

=LET(Filtrato,FILTER(B1:L50,J1:J50<>""),VLOOKUP("sLPC1",Filtrato,9,FALSE))

 

[Peter_SSs]

Welcome to the MrExcel board!

Like @Anthony47's suggestion, I would not use whole column references for the suggestion below, but you may need to edit the '50' row values to be sure to be big enough for your data.

=INDEX(FILTER(J2:J50,(B2:B50="sLPC1")*(J2:J50<>""),""),1)