Find Non-Blank Cell

[ljybrcd]

I have read some of the other posts, but can't seem to get the suggested formulas to work right. I've tried using vlookup, isblank, index/match, etc.

I have a summary sheet in one tab where I want to lookup a tank number in the first column and get the first non-blank result for that tank number in a column from a table in the second tab.

The First tab is a list of results for each Tank number (Tank in column A, results in columns B and C).
The Second tab is all the results in a large table of data like the example below.
So, I want excel to retrieve the "14.1" result in the "code 1" column for all the rows that match Tank "L001".

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]tank[/TD]
[TD]code 1[/TD]
[TD]code 2[/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD][/TD]
[TD]15.2[/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD][/TD]
[TD]15.5[/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD]14.1[/TD]
[TD]15.7[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!

 

[AlphaFrog]

Confirm with Control+Shift+Enter

=INDEX(B2:B4,MATCH("L001",IF(B2:B4="","",A2:A4),0))

 

[ljybrcd]

No, sorry that did not work. My index range is on sheet #2 D2:AV1300, with the Tank number in column d and results in columns E thru AV. The formula and lookup value for the Tank number is on sheet #1 in column A. I adjusted for formula but got an "REF" error result"
{=[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]INDEX('Lab eport'!$D$1:$AV$1300,MATCH(A:A,IF('Lab Report'!$D$1:$AV$1300="","",A:A),0))[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Try to post the data from D1:H10 of Lab Report.

 

[ljybrcd]

[TABLE="width: 425"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD]TANK[/TD]
[TD]OpCode[/TD]
[TD]Alc NIR[/TD]
[TD]Alc GC[/TD]
[TD]TA[/TD]
[TD]pH[/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD]Post Job Check[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.58[/TD]
[TD]3.69[/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD]Post Job Check[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.58[/TD]
[TD]3.69[/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD]Post Job Check[/TD]
[TD]14.37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD]Post Job Check[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD]Post Job Check[/TD]
[TD]14.38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]L001[/TD]
[TD]Post Job Check[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is a portion of columns D-I. Thanks for your help!

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	TANK	OpCode	Alc NIR	Alc GC	TA	pH			Alc NIR	Alc GC	TA	pH
2	L001	Post Job Check			0.58	3.69		L001	14.37		0.58	3.69
3	L001	Post Job Check			0.58	3.69
4	L001	Post Job Check	14.37
5	L001	Post Job Check
6	L001	Post Job Check	14.38
7	L001	Post Job Check
Sheet1

In I2 control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($C$2:$F$7,MATCH(1,IF($A$2:$A$7=$H2,IF(INDEX($C$2:$F$7,0,MATCH(I$1,$C$1:$F$1,0))<>"",1)),0),MATCH(I$1,$C$1:$F$1,0)),"")

 

[ljybrcd]

Ok, I see how that works with the set up shown above. But if I edit the formula to include 2000 rows of data (ex: $c$2:$f$2000, instead of the 2 instances of $c$2:$f$7) then the formula shows blank. What would cause that?

 

[Aladin Akyurek]

Ok, I see how that works with the set up shown above. But if I edit the formula to include 2000 rows of data (ex: $c$2:$f$2000, instead of the 2 instances of $c$2:$f$7) then the formula shows blank. What would cause that?

When we edit such a formula, we again need to confirm the formula with control+shift+enter.

 

[ljybrcd]

Oops, rookie move, thanks! Okay, so I have edited the formula to include all rows of the Lab Report data in the relevant columns. But now it only seems to show the result for anything in the Tank column that is blank, vs "L001" in the Tank column. Here's the formula I edited:
{=IFERROR(INDEX('Lab Report'!$F:$AV,MATCH(1,IF('Lab Report'!$D:$D=$A:$A,IF(INDEX('Lab Report'!$F:$AV,0,MATCH('Lab Report'!F$1,'Lab Report'!$F$1:$AV$1,0))<>"",1)),0),MATCH('Lab Report'!F$1,'Lab Report'!$F$1:$AV$1,0)),"")}

Just a reminder that my formula is on a separate sheet from the data in the Lab Report. The Lab Report table starts in column D containing the Tank IDs, but the Tank ID on the summary sheet is in column A.

 

[Aladin Akyurek]

The data sheet: Lab Report
The formula sheet: Sheet1

Book1
	A	B	C	D	E
1		Alc NIR	Alc GC	TA	pH
2	L001	14.37		0.58	3.69
Sheet1

In B2 control+shift+enter, not just enter, and copy across:

=INDEX('Lab Report'!$F:$AV,MATCH(1,IF('Lab Report'!$D:$D=$A2,IF(INDEX('Lab Report'!$F:$AV,0,MATCH(B$1,'Lab Report'!$F$1:$AV$1,0))<>"",1)),0),MATCH(B$1,'Lab Report'!$F$1:$AV$1,0))

Note 1. The differences between this and your edit (see post #9 ) are bolded.
Note 2. A best practice is that the whole column references should be avoided for efficiency reasons. The need for such
can be effectively circumvented by having dynamic named ranges.