excel - Doing an array formula lookup -


i have list of data this:

name   |    number bob    |   300 joe    |   200 jane   |   400 sisqo  |   450 jill   |   500

there's 62 rows of this, numbers can different, repeated. goal add column contains list of people @ 400 or above. looks this:

name   jane  sisqo  jill

what have is: 

{=iferror(index($a$2:$b$6, small(if($b$2:$b$6 >= 400, $b$2:$b$6,), row(1:1)), 1), "")}

what imagine doing: 1) small function looking in range b2:b6, , because array formula, looks @ each cell in b2:b6 see if greater or equal 400. 2) don't know how, hope row function finding array of b2:b6 if value in cell under consideration greater or equal 400. if doesn't find anything, nothing happens. 3) runs comparison small(b2:b6, 1) lowest value in range. when copied down, because i'm using row() function, small(b2:b6, 2), small(b2:b6, 3) , on.

at point row of lowest number @ 400 or below should found.

so index function should read 

index(a2:b6, 3, 1)

for first one.except i'm getting

name
bob bob

so error?

i prefer use match in array form:

=iferror(index($a$2:$a$6,match(1,(countifs($e$1:e1,$a$2:$a$6)=0)*($b$2:$b$6>=400),0)),"")

being array formula must confirmed ctrl-shift-enter instead of enter when exiting edit mode. if done correctly excel put {} around formula.

enter image description here


Comments

Post a Comment

Popular posts from this blog

sql server - Cannot query correctly (MSSQL - PHP - JSON) -

i have little problem on project. , turns out query result returns nothing when value of first name or last name has 'ñ' in it. here's code config.php: <?php ini_set('mssql.charset', 'utf-8'); header('content-type: text/html; charset=utf-8'); $servername = "192.168.1.21"; /* ip add of db server */ $connection = array("database" => "db", "uid" => "?", "pwd" => "***"); $conn = sqlsrv_connect($servername, $connection); if(!$conn){ echo "connection not established."; die(print_r(sqlsrv_errors(), true)); } ?> myquery: $idnumber = $_post["idnum"]; $response = array(); $query2 = "select clname, cfname, cmname, cqualifier student cidno = '$idnumber'"; try{ $stmt2 = sqlsrv_query($conn, $query2); } catch(pdoexeption $ex){ $response["success"] = 0; $response["message"] = "d...
Read more

php - trouble displaying mysqli database results in correct order -

i have table in database stores events , date expire. want display events on website display current events first expired events after? i have tried following sql $sql = "select noticeid, notice, noticedesc, noticeimg, noticedate, expirydate tbl_notices group noticeid ,expired order expirydate asc"; but returns expired results first due expirydate being ordered asc in query. my tbl_notice structure looks this noticeid => int(4) notice => varchar(100) noticedesc => text noticedate => timestamp noticeimg => varchar(40) expirydate => datetime expired => int(1) 0 current or 1 expired my php code is $sql = "select noticeid, notice, noticedesc, noticeimg, noticedate, expirydate tbl_notices group noticeid ,expired order expirydate asc"; $result = mysqli_query($conn,$sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { ...
Read more

C++ Linked List -

i trying create linked list download txt file , use linked list handle file line line. when handling downloaded linked list operations performed on such text editor would. encountering problems however. seems "node(string value)" section of code has wrong though original node() declaration no arguments passes. unable figure out quite is. node.h class node { public: node(); node(string value); void setnext(node *nextnode); // allows user set "next" pointer of node points friend class linkedlist; private: string data; // data box node* next; // pointer box }; node.cpp # include <string> # include "node.h" using namespace std; node::node() { data = ""; next = null; } node::node(string value) { data = value; next = null; } void node::setnext(node *nextnode) // allows user set "next" pointer of node points { this->next = nextnode; } your #include <string> ...
Read more