# need formula

A1=Skip B1= 6 A2=Dave B2=5 A3=Walt B3=7 A4= John B4=4

Need A7= John B7= 4 ( Lowest value line 4 )

edit retag close merge delete

Sort by » oldest newest most voted

In B7 = =SMALL(B1:B4;1), In A7 = =INDEX(A1:A4;(MATCH(1;(B1:B4=B7);0)))

## -----------------------------------------------------------------------------

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

more

this worked on Libre office it doesn't work on my other computer that runs on excel. Is there another format for excel I keep getting a #value error

( 2019-08-16 16:13:25 +0200 )edit

Excuse me I can't help Excel, I've been working with LibreOffice since 1998, it was StarOffice.

( 2019-08-16 16:18:23 +0200 )edit
1

Note that the formula expressions given contain extraneous spaces within the range references, which does not work (plus unnecessary parentheses but those don't harm). Note also that here per convention we use ; semicolon parameter separator (though the given first formula uses comma instead) as that works in all locales in LibreOffice. In Excel you may have to use , comma separator instead. The corrected formulas for LibreOffice are

• A7: =INDEX(A1:A4; MATCH(1; B1:B4 = B7; 0))
• B7: =SMALL(B1:B4;1)
( 2019-08-16 17:40:53 +0200 )edit

Actually the formula in A7 can be simplified to =INDEX(A1:A4; MATCH(B7; B1:B4; 0)) Maybe that's even another cause why it doesn't work in Excel because Excel has distinct boolean logical types (the result of the B1:B4=B7 comparison) for which looking up a number 1 does not match.

( 2019-08-16 20:53:14 +0200 )edit

Thanks works grear

( 2019-08-17 16:57:28 +0200 )edit