Thread: Look for a value in previous ranges and display row difference

Hello,

I'd like to check a value against previous ranges, find the first occurence and receive the row difference.

For example,

34 35 45 56 57 64 65
16 23 36 38 56 65 70
25 27 37 54 60 66 68
12 32 38 47 52 57 70
18 25 37 39 50 51 54
23 20 21 22 58 59 62
25 37 22 56 64 57 59

Checking the last range, the result for number 25 is 2 as number 25 appears two rows before.

So the result for all the values in the last range is

2 2 1 5 6 3 1


I have been trying to get this formula to work

=IFERROR(ROW()-LOOKUP(2,1/($O$1:$O4927=$O4928),ROW($B$1:$B4927)),ROW()))

which I have from another member here on the forum which looks for a value in one column only and returns the row difference. I have tried changing the formula to

=IFERROR(ROW()-LOOKUP(2,1/($O$1:$U4927=$O4928),ROW($A$1:$A4927)),ROW()))

but I get an incorrect result.


Thank you,
Sans

You are working on some interesting project..

Try:

=ROW($O4928)-MAX(($O$1:$O4927=$O4928)*ROW($O$1:$O4927))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied across.