Posts by Hestar

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

    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

  • 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!