Formula to return nth value in a row

[Roy_Excel_Island_Apps]

Hi guys,

thanks for helping me out!

Example: There are values in multiple columns in row 1. I need a formula that returns the first value in that row.
Therefore I found this formula that works:
=INDEX(1:1;MATCH(TRUE;INDEX(ISNUMBER(1:1);0);0))

But now I need a formula that returns the SECOND value in this row. How do I get it? I will also need a formula to get the 3rde, 4th, ... value in a row.

Thanks in advance!
Roy

 

[James006]

Hi,
You do need to use =SMALL(IF($A$1:$A$20="criteria",ROW($A$1:$A$20),""),K))
K will take your values 1,2,3,4 ...
Hope this will help

 

[Fluff]

Another option which will return all the numbers

=FILTER(A1:Z1,ISNUMBER(A1:Z1))

 

[Roy_Excel_Island_Apps]

Hi,
You do need to use =SMALL(IF($A$1:$A$20="criteria",ROW($A$1:$A$20),""),K))
K will take your values 1,2,3,4 ...
Hope this will help

What do I fill in as the criteria? And where do I use K for? If I need the second number from row 1?

 

[Fluff]

If you just want the 2nd number, how about

=INDEX(FILTER(A1:Z1,ISNUMBER(A1:Z1)),,2)

 

[Roy_Excel_Island_Apps]

If you just want the 2nd number, how about

=INDEX(FILTER(A1:Z1,ISNUMBER(A1:Z1)),,2)

That's it @Fluff !!! THANK YOU!!!

 

[Fluff]

You're welcome & thanks for the feedback.