Lookup functions excel 2013: Non VBA

[debruintjie]

Hi
See below table.

Column	A	B	C	D	E	F	G	H	I	J	K
Row 1	236	533	112			236				533	112

<tbody>
</tbody>

I need a formulas for cell A1, B1 and C1 to include the first value in range E:J exceeding zero/non blank in A1, the second value in range E:J exceeding zero/non blank in B1 and the third value in range E:J exceeding zero/non blank in C1.

Kind regards

 

[davesweep]

Hello,

I assume from your example you require E1:K1 and not E1:J1

A1 =INDEX(E1:K1,SMALL(IF(E1:K1<>"",COLUMN(E1:K1)-COLUMN(E1)+1),2))

B1 =INDEX(E1:K1,SMALL(IF(E1:K1<>"",COLUMN(E1:K1)-COLUMN(E1)+1),2)) : An array. Confirm with ctrl shift and enter

C1 =INDEX(E1:K1,SMALL(IF(E1:K1<>"",COLUMN(E1:K1)-COLUMN(E1)+1),3)) : An array. Confirm with ctrl shift and enter

 

[Aladin Akyurek]

A1, control+shift+enter, not just enter, and copy across to C1:

=IFERROR(INDEX($E$1:$K$1,SMALL(IF(ISNUMBER($E$1:$K$1),
  COLUMN($E$1:$K$1)-COLUMN($E$1)+1),COLUMNS($A$1:A1))),"")

 

[johnmpl]

Hi to everyone!

The same concept of Aladin Akyurek, but with AGGREGATE (this formula not require Ctrl + Shift + Enter):

=IFERROR(INDEX($E$1:$K$1,AGGREGATE(15,6,(COLUMN($E$1:$K$1)-COLUMN($E$1)+1)/ISNUMBER($E$1:$K$1),COLUMNS($A1:A1))),"")

Blessings!

 

[debruintjie]

Thanks guys. 2nd option worked quite well.

Regards

 

[Aladin Akyurek]

Thanks guys. 2nd option worked quite well.

Regards

What is "2nd option"?