# Excel: Return last positive value on row, and another value related to it

I have a sheet on which values relating to website URL redirect chains are returned. At the end of each row, I want to return the final URL and its HTTP response. These values are populated by `VLOOKUP`s done on other sheets, so where a chain is short, the "blank" cells aren't strictly blank.

From the example above, I'd want to return the values `www.example.com/next-4/` and `200`. I've been trying to adapt things like this without success.

NB: In case it makes a difference, the hash values are there because some of the URLs are too long to `VLOOKUP`, so the lookups are done on hashes.

Thank you.

-

You could use =MATCH(). As the final cell is a number, less than 999, you could do this (assuming we are targetting row 2, and the maximum columns a chain might go to is "Z"):

``````=MATCH(999,A2:Z2)
``````

This will find the last number that is less than or equal to 999, and returns its index in the range. For your example row, it would return `14` as N is column 14, and it contains the last value less than 999.

So to get the response code, you can use:

``````=INDEX(A2:Z2,0,MATCH(999,A2:Z2))
``````

This returns `200`.

And to get the url:

``````=INDEX(A2:Z2,0,MATCH(999,A2:Z2)-2)
``````
-
That's really good, thank you. The only snag is that the `VLOOKUP` returns a `0` for URL after the final one is reached, which seems to upset things, but some means of making sure that 0 is removed should cure that. –  GDav Dec 19 '12 at 14:21

If column I last value is always text then you could use LOOKUP to get the last text value in that column

`=LOOKUP("zzz",I:I)`

then if you want the value from the same row in column N try

`=LOOKUP("zzz",I:I,N:N)`

-