VLOOKUP OR INDEX? Duplicate values exist, keep non-blank value (text & numbers)

[CCAmy]

I have a very large spreadsheet that I am trying to return the value of some columns based on matching depths. Tab1 of my spreadsheet has no duplicate depths while Tab2 DOES have duplicate depths, some of which the columns have null values.

The attached Example Tab1 image has a yellow highlighted row that SHOULD contain data but, because the first value at this matching depth is null on the Example Tab 2 image, the result is blank. How do I get it to skip the null values and return the first non-null value? I have snagged my VLOOKUP statement in the image.

NOTE: Some columns will have text and others will be numbers. According to what I've read, I may need to use INDEX instead but I have yet been able to make heads or tails of the examples I've seen. Also, sorting is not an option as I have close to 500 columns to process.

Thank you for ANY help!

 

[pjmorris]

Have you tried using a pivot? Take the Depth Rounded Column as the Rows element and add the relevant columns to the Values area. That said, this might be tedious if you have over 500 columns (seems vast) to add to the values area. And what happens if a particular 'Depth Rounded' value has two or more lines with values in and one or more lines of blank or are there only ever no more than 2 lines (one blank and one with a value)?

Final thought, this may be much easier by creating your second table from the data table using Power Query - you can simply delete the rows with blanks.

Book.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Depth Rounded	PO1	Po2	PO3	PO4	PO5			Depth Rounded	Sum of PO1	Sum of Po2	Sum of PO3	Sum of PO4	Sum of PO5
2	12359.1								12359.1
3	12359.2	2	4	6	8	10			12359.2	2	4	6	8	10
4	12359.3								12359.3	1	2	3	4	5
5	12359.3	1	2	3	4	5			12359.4	6	7	8	9	10
6	12359.4	6	7	8	9	10			12359.5
7	12359.5								12359.6
8	12359.6								12359.7
9	12359.7								12359.8
10	12359.8								12359.9
11	12359.9								12360
12	12360								12360.1
13	12360.1								12360.2
14	12360.2								12360.3
15	12360.3								Grand Total	9	13	17	21	25
Sheet3

 

[AhoyNC]

What version of Excel are you using?
If you have Excel 365 with the FILTER function then try this instead of the formula below.

IFERROR(FILTER(Sheet2!$B$2:$G$7,(Sheet2!$A$2:$A$7=Sheet1!$A2)*(Sheet2!$B$2:$B$7<>0)),"")

With the formula below copy the formula in C2 down and across.

Sheet1

Book1
	A	B	C	D	E	F	G	H
1	Depth_Rounded	Depth_Rounded	PO1	PO2	PO3	PO4	PO5	PO2-5
2	12531.3	12531.3
3	12531.4
4	12531.5
5	12531.6	12531.6	2.49	0.4	0.45	0.27	0	1.12
6	12531.7
7	12537		2.3	0.42	0.41	0.26	0	1.19
8	12531.9
Sheet1

Cell Formulas
Range	Formula
C2:H8	C2	=IFERROR(INDEX(Sheet2!$B$2:$G$6,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1)/((Sheet2!$A$2:$A$7=Sheet1!$A2)*(Sheet2!$B$2:$B$7<>0)),1),MATCH(Sheet1!C$1,Sheet2!$B$1:$G$1,0)),"")

Sheet2

Book1
	A	B	C	D	E	F	G
1	Depth_Rounded	PO1	PO2	PO3	PO4	PO5	PO2-5
2	12531.3
3	12531.6
4	12531.6	2.49	0.40	0.45	0.27	0.00	1.12
5	12534.1	1.12	0.15	0.30	0.17	0.00	0.61
6	12537.0	2.3	0.42	0.41	0.26	0.00	1.19
7	12539.8
Sheet2

 

[Peter_SSs]

If you have Excel 365 with the FILTER function then try this instead of the formula below.
IFERROR(FILTER(Sheet2!$B$2:$G$7,(Sheet2!$A$2:$A$7=Sheet1!$A2)*(Sheet2!$B$2:$B$7<>0)),"")

Hi AhoyNC
Just a couple of comments ..

• Since this formula is on Sheet1 it doesn't need the Sheet1! reference. In fact it is a bad idea to include it. To see why, have a look at the example in post 11 here
• You don't need the IFERROR with the FILTER function - at least not in this case if you have included it to deal with the situation where the FILTER function does not return any results. You can just use the optional 3rd argument of the FILTER function instead.
  
  =FILTER(Sheet2!$B$2:$G$7,(Sheet2!$A$2:$A$7=$A2)*(Sheet2!$B$2:$B$7<>0),"")

 

[CCAmy]

Have you tried using a pivot?

The pivot table would definitely be too tedious and the other example did not include any duplicate depths so it is not applicable to my problem.

 

[CCAmy]

Sorry - I see the duplication now. Your example show a whole row that is blank. How does it handle it if only some columns are blank?

 

[CCAmy]

What version of Excel are you using?
If you have Excel 365 with the FILTER function then try this instead of the formula below.

IFERROR(FILTER(Sheet2!$B$2:$G$7,(Sheet2!$A$2:$A$7=Sheet1!$A2)*(Sheet2!$B$2:$B$7<>0)),"")

With the formula below copy the formula in C2 down and across.

Sheet1

Book1
	A	B	C	D	E	F	G	H
1	Depth_Rounded	Depth_Rounded	PO1	PO2	PO3	PO4	PO5	PO2-5
2	12531.3	12531.3
3	12531.4
4	12531.5
5	12531.6	12531.6	2.49	0.4	0.45	0.27	0	1.12
6	12531.7
7	12537		2.3	0.42	0.41	0.26	0	1.19
8	12531.9
Sheet1

Cell Formulas
Range	Formula
C2:H8	C2	=IFERROR(INDEX(Sheet2!$B$2:$G$6,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1)/((Sheet2!$A$2:$A$7=Sheet1!$A2)*(Sheet2!$B$2:$B$7<>0)),1),MATCH(Sheet1!C$1,Sheet2!$B$1:$G$1,0)),"")

Sheet2

Book1
	A	B	C	D	E	F	G
1	Depth_Rounded	PO1	PO2	PO3	PO4	PO5	PO2-5
2	12531.3
3	12531.6
4	12531.6	2.49	0.40	0.45	0.27	0.00	1.12
5	12534.1	1.12	0.15	0.30	0.17	0.00	0.61
6	12537.0	2.3	0.42	0.41	0.26	0.00	1.19
7	12539.8
Sheet2

Darn....I'm running 2016

 

[AhoyNC]

If a column is blank the formula will return the values in the other columns. The blank column will return a 0 to Sheet1.
What do you want to happen?

 

[pjmorris]

Sorry - I see the duplication now. Your example show a whole row that is blank. How does it handle it if only some columns are blank?

The default is that it will sum them together, though other behaviours are possible. That said, what do you want to happen?

 

[AhoyNC]

If some cells in a column can be blank then the formula I gave above will not work if a cell in column B is blank, but there is data in cells C - H in the same row.
One way around this would be to add a helper column on sheet B as in the example below. Also note the change of formula in cell C2.

IFERROR(INDEX(Sheet2!$B$2:$G$6,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1)/((Sheet2!$A$2:$A$7=$A2)*(Sheet2!$I$2:$I$6)>0),1),MATCH(C$1,Sheet2!$B$1:$G$1,0)),"")

Sheet1

Book1
	A	B	C	D	E	F	G	H
1	Depth_Rounded	Depth_Rounded	PO1	PO2	PO3	PO4	PO5	PO2-5
2	12531.3	12531.3
3	12531.4
4	12531.5
5	12531.6	12531.6	0	0.4	0.45	0.27	0	1.12
6	12531.7
7	12537		2.3	0.42	0	0.26	0	1.19
8	12534.1		1.12	0.15	0.3	0.17	0	0.61
Sheet1

Cell Formulas
Range	Formula
C2:H8	C2	=IFERROR(INDEX(Sheet2!$B$2:$G$6,AGGREGATE(15,6,(ROW(Sheet2!$A$2:$A$7)-ROW(Sheet2!$A$2)+1)/((Sheet2!$A$2:$A$7=$A2)*(Sheet2!$I$2:$I$6)>0),1),MATCH(C$1,Sheet2!$B$1:$G$1,0)),"")

Sheet2

Book1
	A	B	C	D	E	F	G	H	I
1	Depth_Rounded	PO1	PO2	PO3	PO4	PO5	PO2-5		Sum
2	12531.3								0
3	12531.6								0
4	12531.6		0.4	0.45	0.27	0	1.12		2.24
5	12534.1	1.12	0.15	0.3	0.17	0	0.61		2.35
6	12537	2.3	0.42		0.26	0	1.19		4.17
7	12539.8								0
Sheet2

Cell Formulas
Range	Formula
I2:I7	I2	=SUM(B2:G2)