search first value in range

[danovkos]

hi, all,
pls. i have easy question.
How can i search in range and return the first value in range.
F.e.
i have data where in range can be blank cells (i dont know how many - in this case is A1 blank)

A B C D
1 ban 25 pin

my range will be A1: D1
formula will be in E1 and return ban.

how can i do this pls?
thx

[mikerickson]

This CSE formula should do what you want, but there should be a neater method.

=OFFSET(A1, 0, MOD(MATCH(0, (SIGN(LEN(A1: D1))-SIGN(LEN(A1)))), 4))

This needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)

[danovkos]

thank you for help.
but this doesnt works.
this is my real formula

=OFFSET(I4139; 0; MOD(MATCH(0; (SIGN(LEN(I4139:AX4139))-SIGN(LEN(I4139)))); 4))
which i made based yours. I use shift+enter and it gives me a number. But only if its number in first cell. Else return 0.

[mikerickson]

The second argument of MOD should be the number of cells
=OFFSET(I4139; 0; MOD(MATCH(0; (SIGN(LEN(I4139:AX4139))-SIGN(LEN(I4139)))); 42))

[danovkos]

this works oposite
if is value in first it gives me 0
but if is value in other it gives some number but not correct

[mikerickson]

It works for me.
Are you confirming the formula with Ctrl-Shift-Enter?

[Aussiebear]

I use shift+enter and it gives me a number.

As suggested please use Ctrl + Shift + Enter

[danovkos]

yes, i copy the code from here again and again and try ctlr+shift + enter again and again and still is there 0.
But if i try it in new sheet with fake data it works :(. Whats wrong?

[mikerickson]

This works for me

[danovkos]

ok,
what i figuret out
i use your new code and put it in my table.

=OFFSET(I4142; 0; MOD(MATCH(0; (SIGN(LEN(I4142:AX4142))-SIGN(LEN(I4142)))); COLUMNS(I4142:AX4142)))

if i have first data in row in column I, it is return 0, if i start my data in column J, it works good.