Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 VLOOKUPs done on other sheets, so where a chain is short, the "blank" cells aren't strictly blank.

enter image description here

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.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

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)
share|improve this answer
    
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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.