# Lookup functions excel 2013: Non VBA

#### debruintjie

##### New Member
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

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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

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))),"")
``````

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!

Thanks guys. 2nd option worked quite well.

Regards

Thanks guys. 2nd option worked quite well.

Regards

What is "2nd option"?

Replies
3
Views
968
Replies
4
Views
925
Replies
0
Views
536
Replies
3
Views
873
Replies
1
Views
372

1,207,259
Messages
6,077,348
Members
446,279
Latest member
hoangquan2310

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back