0

I don't know if the question must be put here or on SO. Sorry if i make a mistake.

I have to search the first and last occurrence of a certain value within a column(array). I have managed to make use of Index and Match functions to return the first value at left of the searched column (right vlookup), but now i'm having trouble to invert the search order of the lookup (to instead of finding the first occurrence, find the last occurrence).

V = -2

freq        DB
557         -1
558         -2
559         -2
559         -1

INDEX(A3:A6;(MATCH(A1;B3:B6;0))) is my "rigth vlookup"
Improve this question
2
  • Is the column freq sorted? You could use an array formula for that, but it'll be easier if it is sorted.
    – Jerry
    Commented Jul 18, 2014 at 11:32
  • Yes, the freq column is the frequency which is ascending.
    – Guest01
    Commented Jul 18, 2014 at 11:33

1 Answer 1

Reset to default
1

You can use an array formula and MAX to get the last freq since this column is in ascending order, the last freq will be the highest freq:

=INDEX(A3:A6,MATCH(MAX(IF(B3:B6=A1,A3:A6)),A3:A6,0))

Note: Since the above is an array formula, instead of evaluating it with Enter, press and hold Ctrl+Shift and then Enter

MAX(IF(B3:B6=A1,A3:A6)) retrieves the highest freq with the desired DB value.

Improve this answer
3
  • In the if condition IF(B3:B6=B1,A3:A6) B1 is the value i want to lookup? (in my example is -2 A1)
    – Guest01
    Commented Jul 18, 2014 at 11:50
  • @Guest01 Oh yes oops. Sorry about that. I had V in A1 in my test worksheet to confirm the formula. I can write them without testing, but I prefer making sure they work :)
    – Jerry
    Commented Jul 18, 2014 at 11:55
  • @Guest01 No problem. I was travelling and couldn't get on till now.
    – Jerry
    Commented Jul 18, 2014 at 14:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .