Lookup functions excel 2013: Non VBA

debruintjie

New Member
Joined
Sep 9, 2014
Messages
5
Hi
See below table.

ColumnABCDEFGHIJK
Row 1236533112236533112

<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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

davesweep

Well-known Member
Joined
Apr 30, 2007
Messages
510
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
A1, control+shift+enter, not just enter, and copy across to C1:
Rich (BB code):
=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

Board Regular
Joined
Jun 14, 2013
Messages
235
Hi to everyone!

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

Code:
=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,911
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top