Google Spreadsheet Reverse Match

I’m not going to lie to you, but this is easier in Excel. Simply do a Reverse Match by doing =Match(1/range) or even a Lookup (really easy) or something similar.

However, Google Spreadsheets do no work nicely with a reverse match and it does not have any equivalent function to LOOKUP.

Firstly be aware that this formula returns an integer, which is a row number that relates to the row the formula is being calculated in. In a way it returns the exact same result as match, but going up. Simple.

So how do you do this?

By swapping text.

Let’s take a set of data: This is a simple row being delimited by “,”.This could be a column or row, however you need to do this.

1,2,3,4,5,6,7

(The only REVERSE type function I could find is for text, and you can go search for it under Insert > Function)

So let us join the text:

=JOIN(“,”,A1:G1)

=(1,2,3,4,5,6,7)

What we need to do now is look for something and because this is a text string we need to use find.

=FIND(3,JOIN(“,”,A1:G1))

=5

So it finds itself at character position 5.

Now to reverse.

=REVERSE(JOIN(“,”,A1:G1))

=7,6,5,4,3,2,1

=FIND(3,REVERSE(JOIN(“,”,A1:G1)))

=9

Great, now you know the position in the string, but that does not help anything yet, we need to count the number of occurrences, but how?

By doing the following: Take the left portion of the string up to the thing you are looking for, we are looking for 3… so..

=LEFT(REVERSE(JOIN(“,”,A1:G1)),FIND(3,REVERSE(JOIN(“,”,A1:G1))))

=7,6,5,4,3           Tada!!

Now to count the rows. We do this by using the delimiter we defined in the JOIN.

But how to count this.. By using the length of the string and counting it after you remove the delimiter.. WTF?! By counting the difference in length before and after removing the delimiter you count the delimiter.. and if it does not make sense you should not be reading this.

=LEN(LEFT(REVERSE(JOIN(“,”,A1:G1)),FIND(3,REVERSE(JOIN(“,”,A1:G1)))))-LEN(SUBSTITUTE(LEFT(REVERSE(JOIN(“,”,A1:G1)),FIND(3,REVERSE(JOIN(“,”,A1:G1)))),”,”,””))

=4

So we now add 1 to make up for the first row not being counted

=LEN(LEFT(REVERSE(JOIN(“,”,A1:G1)),FIND(3,REVERSE(JOIN(“,”,A1:G1)))))-LEN(SUBSTITUTE(LEFT(REVERSE(JOIN(“,”,A1:G1)),FIND(3,REVERSE(JOIN(“,”,A1:G1)))),”,”,””)) + 1

Simple isn’t it. No it’s not, it took me 4 hours to figure out, hope this made it less for you.

Here’s mine:

=IF(O14=”ENTER SHORT” ,iferror(offset(C14,-(len(left(reverse(join(“,”,indirect(“O10:O”&ROW()-1&””))),FIND(reverse(“EXIT SHORT”) ,reverse(join(“,”,indirect(“O10:O”&ROW()-1&””))), 1)))-len(substitute(left(reverse(join(“,”,indirect(“O10:O”&ROW()-1&””))),FIND(reverse(“EXIT SHORT”) ,reverse(join(“,”,indirect(“O10:O”&ROW()-1&””))), 1)),”,”,””))+1),0),C9) ,IF(O14=”ENTER LONG”,iferror(offset(C14,-(len(left(reverse(join(“,”,indirect(“O10:O”&ROW()-1&””))) ,FIND(reverse(“EXIT LONG”),reverse(join(“,”,indirect(“O10:O”&ROW()-1&””))), 1)))-len(substitute(left(reverse(join(“,”,indirect(“O10:O”&ROW()-1&””))) ,FIND(reverse(“EXIT LONG”),reverse(join(“,”,indirect(“O10:O”&ROW()-1&””))), 1)),”,”,””))+1),0),C9) ,””))

I am looking for “EXIT LONG” and in order to find it in a reversed text string I need to FIND(reverse(“EXIT LONG”)

Hope this helps someone somewhere. Feel free to comment on this.

Adrian