Vlookup to return First Non-Zero Value in Array

[SammiBoi]

I want to create a vlookup formula but instead of returning the first number in array, I want it to return the first number that is non-zero in the array

EG: I want vlook to return $1 for product ABC and $3 for product DEF.

Product | Jan | Feb | March |
-----------------------------------------
ABC | $0 | $1 | $2 |
DEF | $3 | $4 | $5 |


Is this possible perhaps with an array formula?

 

[DonkeyOte]

One possibility:

Excel Workbook
	A	B	C	D
1	Product	Jan	Feb	March
2	ABC	0	1	2
3	DEF	3	4	5
4
5	Product
6	ABC	1
Sheet3

 

[Domenic]

Here's a similar way...

B6, confirmed with CONTROL+SHIFT+ENTER:

=INDEX($B$2:$D$3,MATCH($A6,$A$2:$A$3,0),MATCH(TRUE,INDEX($B$2:$D$3,MATCH($A6,$A$2:$A$3,0),0)>0,0))

Actually, if you want to return the results on the same row, maybe...

A1:E3...

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=320><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Product

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Jan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>Feb</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>March</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>ABC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>DEF</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>3</TD></TR></TBODY></TABLE>

E2, confirmed with CONTROL+SHIFT+ENTER, and copy down:

=INDEX(B2:D2,MATCH(TRUE,B2:D2>0,0))

 

[fju2112]

what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".

 

[T. Valko]

what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".

Can you post some sample data and let us know what result you expect?

 

[Aladin Akyurek]

what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".

Perhaps...

=INDEX(B2:D2,MATCH(TRUE,B2:D2<>"",0))

You need to apply control+shift+enter to the formula, that is, press down the control and shift keys at the same time while you hit the enter key. When done properly, a pair of curly braces, { and }, appear around the formula.

If the foregoing is not what you want, try to elaborate by means of a small sample.

 

[T. Valko]

what if i have text values i want to return using this formula? It's not working; I just get "#VALUE!".

Can you post some sample data and let us know what result you expect?

Is this what you had in mind?

Book1
	A	B	C	D	E	F
1	_	this	text	_	_	this
2	that	none	_	some	_	that
3	_	_	_	other	_	other
4	_	_	stuff	more	_	stuff
Sheet1

This formula entered in F1 and copied down:

=INDEX(A1:D1,MATCH("*",A1:D1,0))