T
T
tartarelin2014-02-13 14:16:58
excel
tartarelin, 2014-02-13 14:16:58

How to implement last match search in excel column?

There is a log of calls, using it in excel I make a table with statistics of calls, from which number, how many outgoing calls and I wanted to add what time the first call was, what time the last one.
In excel, knowledge is superficial, and not only in it, google helps out, quickly found a solution for the first call and quickly applied it
= INDEX (PBX! F: F; MATCH (B6; PBX! C: C; 0))
everything is as easy as shelling pears , MATCH looks for phone number B6 in column PBX!C:C with an exact match of 0, results in the row number where this phone number first occurs, and using INDEX, gets the time from column PBX!F:F and the found row.
=INDEX(PBX!F:F;MATCH(2,1/(PBX!C:C=B6)))
and this is the solution from google for the second task, find the time of the last call, i also quickly applied it and it works, but i can't figure out how it works.
Instead of the argument by which the search is performed here "2", it is not clear why two, why two, and the range where to search is set using 1/(PBX!C:C=B6), which is also incomprehensible to me.
Can someone explain to me in a popular way how this formula works =INDEX(PBX!F:F;MATCH(2;1/(PBX!C:C=B6)))?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
rosperitus, 2014-02-14
@rosperitus

1/(PBX!C:C=B6) is not valid because PBX!C:C=B6 is a comparison and returns TRUE or FALSE. The result is a NUMBER type.
MATCH(2;1/(PBX!C:C=B6))) is completely incorrect, looking for a value that is less than or equal to 2 in the array 1/(PBX!C:C=B6), which is not an array.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question