• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Use Offset() to dictate what cell a Vlookup or (index/ match) returns

J

Josh

New Member
Hello. I am new to this site and really love it, good work Chandoo!

I ran into a very frustrating situation where I needed to simply offset the result of a vlookup down one row. I was under a deadline and had to re-work the entire sheet another way to get what I wanted.


=vlookup(a3,'so and so sheet'!A1:D999,3,0) but I wanted the data from one cell below what was being returned


I tried to place the offset formula within the vlookup telling it to return the offset rather than the column number(3). I also tried using an index match formula rather than a vlookup but no luck.


This offset/vlookup tool would have saved me lots of time and impressed my boss. Can anyone help? Oh, and by the way, I tried to copy and paste the sheet as an image on this post but I was not able to.
 
Josh


I'd use a Index/Match combination like this

=+INDEX(A1:D999,MATCH(G8,A1:A999,0)+1,3)

Note that the match will find the matching row for the value in G8

and the +1 after that tells the Index to go down 1 extra row.

Change the Lookup value G8 to suit

The 3 at the end tells you which column to return a value from


Chandoo.org doesn't allow oposting of files or pics but you can share these files by posting on a free web site and then sharing the link

Have a read of: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
You can use that form

I prefer the longer version as it allows you to interactively change the column you are returning values from by replacing the ,3) with a reference ,H8) where H8 can be linked to a Combo Box or Slider etc
 
Thank you all very much. I didn't expect a response so quickly. I will try this formula later today. It sounds like it will work perfectly.
 
The index/match combo is no use when the values being referenced for lookup and the the data being looked up are not separated by the same number of rows. For instance, I have Product A and Product B in A1 and A2, but the array I'm looking up has multiple rows in between Product A and Product B, with several columns of associated data for each.


I suppose you could use that, but it is extremely clumsy. Is there a more elegant solution?
 
You must log in or register to reply here.
Back
Top