Which value occurs first between (value 1) and (value 2) in row

[rma8615]

Hello all,

I work a lot with urls and I am currently stuck. I'm hoping someone can help!

In the example below I have two searches *cart* and *coupons*.
I normally use this

=IF(TYPE(HLOOKUP(B$2,$C5:$K5,1,FALSE))=2, 1,IF(TYPE(HLOOKUP(B$3,C5:$K5,1,FALSE))=2, 1,0))

to find if either search appears and return a 1 or 0.

--

In this case, I want to know which page each person visited first instead of just checking for the values alone. So far, this is what I've come up with

=AND(TYPE(HLOOKUP(B$2,$C5:$K5,1,FALSE)=2),TYPE(HLOOKUP(B$3,$C5:$K5,1,FALSE)=2))

This will return true/false if both occur, but I then want to nest it in an if statement to say,

=IF(AND(TYPE(HLOOKUP(B$2,$C5:$K5,1,FALSE)=2),TYPE(HLOOKUP(B$3,$C5:$K5,1,FALSE)=2))=TRUE,[find which of the searches occurs first in the same range returning either "cart" or "coupons"],"")

Thanks in advance for any help that you can provide!
If there is an easier way to do any of this, it would be much appreciated! Unfortunately, the range of urls also includes the page title and time spent on that page.




[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*cart*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*coupons*[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]Cart or coupons first?[/TD]
[TD]Page 1 url[/TD]
[TD]Page 1 title[/TD]
[TD]Page 1 time spent[/TD]
[TD]Page 2 url[/TD]
[TD]Page 2 title[/TD]
[TD]Page 2 time spent[/TD]
[TD]Page 3 url[/TD]
[TD]Page 3 title[/TD]
[TD]Page 3 time spent[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cart[/TD]
[TD]www.test.com/cart[/TD]
[TD]check out page[/TD]
[TD]101[/TD]
[TD]www.test.com/other[/TD]
[TD]other[/TD]
[TD]25[/TD]
[TD]www.test.com/coupons.aspx[/TD]
[TD]great deal![/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Coupons[/TD]
[TD]www.test.com/coupons.aspx[/TD]
[TD]great deal![/TD]
[TD]109[/TD]
[TD]www.test.com/cart[/TD]
[TD]check out page[/TD]
[TD]38[/TD]
[TD]www.test.com/other[/TD]
[TD]other[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[rma8615]

I ended up solving the problem!

=IF(ISNA(OR(MATCH(DR$5,$GL7:$ME7,0),MATCH(DR$4,$GL7:$ME7,0))),"",IF(MATCH(DR$5,$GL7:$ME7,0)<MATCH(DR$4,$GL7:$ME7,0),1,2))

If you see a shorter way of doing this.. please let me know.

Thanks everybody. (myself included on this one)