Results 1 to 5 of 5

Thread: Trouble with how to lookup text and data in different columns and rows.

  1. #1

    Trouble with how to lookup text and data in different columns and rows.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm trying to look up an exact match for some text (changes cell position daily), then have the data associated with that text shown. The problem I'm having is the associated data is in a cell that is 4 rows down and 3 columns over from the text. I've been trying to use a mix of LOOKUP, MATCH, IF, and INDEX functions and got completely lost. Any help would be greatly appreciated!

    Thanks!

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    Is this what you mean

    =INDEX($J$6:$J$20,MATCH(A2,$G$2:$G$16,0))


    where J6:J20 is where the value to be retreived is, G2:G16 is the lookup range.

  3. #3
    Yes, that is the basic concept. The problem I'm having is that I need the function to look through the entire G column, find the exact text match, then find the value relative to the text position. I don't have any known cell ranges other than the entire column G as the lookup range for the text, and that the value to be retrieved is in column J (has to be relative to text position in column G). The value in column J is 4 rows down and 3 columns over from the cell that the text match is found in column G.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,820
    Articles
    0
    Excel Version
    O365
    How about this

    =INDEX($G:$J,MATCH(A2,$G:$G,0)+4,4)

  5. #5
    You are spectacular! That is exactly what I was looking for the formula to do. Thank you so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •