Using a reference to return a value 'n' columns and rows away from the search result

  • Hi there,

    I've been trying to get my head around VLookup, Match, Index and Offset to try and figure out the answer but really need some help.

    I essentially want to use the VLookup function, but instead of returning a value on the same row and from a certain column from the search result cell, I want to return a value which is 'n' rows and 'n' columns away from the search result cell (the 'n' will be the same for every search result - i.e. the data I'm looking for will always be the same distance away from each search result).

    I can't post the spreadsheet because it's work related but I hope you can see what I mean.

    Thanks for advance for any help!

  • Re: Using a reference to return a value 'n' columns and rows away from the search res


    =OFFSET() is what you need.


    Example for formula in cell A1 picking data from cell D10
    =OFFSET(A1,9,3)


    And if you really want it simple then you enter =D10 in cell A1

  • Re: Using a reference to return a value 'n' columns and rows away from the search res


    Many thanks for the quick reply!


    The problem is I first need to do some kind of search function to initially find the 'A1' in your example, and use offset to return the value a certain number of cells away from the search result 'A1'.

    So I want to use a reference (which is a product code), search a table of data to pick up that product code, and then return a piece of data about that product (e.g. weight) which will always be positioned three rows down and two columns to the right of the product code in the table. This is so I can repopulate the data into a different table which also has the product codes.

    So maybe the formula would look something like:

    =OFFSET(the cell reference of the search result,3,2)

    Thanks again!


    Hestar

  • Re: Using a reference to return a value 'n' columns and rows away from the search res


    =INDEX(column_2_to_the_right,match(lookup_value,lookup_column,0)+3)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Using a reference to return a value 'n' columns and rows away from the search res


    Hi Rory,Just to say your formula worked perfectly. Thank you and everyone else for all your help! (I would have replied earlier but was having trouble trying to post the reply :D)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!