Ask Your Question
0

need formula

asked 2019-08-15 18:16:23 +0200

0for4 gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-08-15 18:36:21 +0200

updated 2019-08-16 17:47:41 +0200

@0for4,

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.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more

Comments

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

0for4 gravatar image0for4 ( 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.

Gilberto Schiavinatto gravatar imageGilberto Schiavinatto ( 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)
erAck gravatar imageerAck ( 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.

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

Thanks works grear

0for4 gravatar image0for4 ( 2019-08-17 16:57:28 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-08-15 18:16:23 +0200

Seen: 28 times

Last updated: Aug 16