+ Reply to Thread
Results 1 to 4 of 4

need help understanding a formula

  1. #1
    Registered User
    Join Date
    11-08-2011
    Location
    NY - New York
    MS-Off Ver
    Excel 2003
    Posts
    4

    need help understanding a formula

    =IF(D7="","",OFFSET($K$7,MATCH(D7,$J$7:$J$36,0)-1,0))

    There's a table that I found doing what I was looking for but I don't understand how it was done. So I have an abbreviation column and insurance company column. In the abbrev column I only need to type FID then when I tab on 'tab', the whole word Fidelis will come up in the right cell under insurance company column.

    There's a table beside the data listing all the abbreviations and insurance companies. So the above formula is what I copied off from the insurance company column. Can someone help me understand how to come up with this fx?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: need help understanding a formula

    I have no idea how the formula relates to the data you are describing--you didn't say what data is in what column--but I can explain what it's doing.

    =IF(D7="","",OFFSET($K$7,MATCH(D7,$J$7:$J$36,0)-1,0))

    Let's work it from the inside out.

    MATCH(D7,$J$7:$J$36,0)

    This looks for the value from D7 in the range of $J$7:$J$36. The 0 means "look for an exact match in an unsorted list." The result is the position of the value of D7 within the range $J$7:$J$36. For example, if the value in D7 is found in J8, the result is 2. For shorthand, let's call this <ValuePosition>.

    Next we have

    OFFSET($K$7,MATCH(D7,$J$7:$J$36,0)-1,0)

    or using our shorthand

    OFFSET($K$7,<ValuePosition>-1,0)

    This returns the value found in a position relative to a cell. The first argument says to start in $K$7, then move by <ValuePosition>-1 rows, and 0 columns. Because the range that we sought D7 in also starts in row 7, this expression finds the value in column K that is in the same row as where we found D7 in column J. With me so far? For shorthand, let's call this whole thing <ValueInK>.

    Now we finally have

    =IF(D7="","",OFFSET($K$7,MATCH(D7,$J$7:$J$36,0)-1,0))

    or using our shorthand,

    =IF(D7="","",<ValueInK>)

    We first test to see if D7 is a blank. If so, the final result is a blank. We have to do this, because next we're going to search for the value, and if we search for a blank we will likely produce an error.

    So if D7 is not blank, it is going to return the value in K that is in the same row where the value of D7 was found in column J.

    Now I will strongly suggest that you curl up with a glass of wine in front of a roaring fire and study the Excel help pages for OFFSET and MATCH.

    BTW I would have written this more simply as

    =IF(D7="","",VLOOKUP(D7,$J$7:$K$36,2,FALSE))

    which I leave as an exercise to the reader.
    Last edited by 6StringJazzer; 11-09-2011 at 12:41 AM. Reason: Changed my preferred solution to use VLOOKUP
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    11-08-2011
    Location
    NY - New York
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: need help understanding a formula

    Very good and thorough explanation. Surprisingly, I understand. Not sure if I am able to replicate the fx in another situation. This might be a stupid question, I understand J7:J36 is something like data from J7 to J36 but why is it $J$7?


    Quote Originally Posted by 6StringJazzer View Post
    I have no idea how the formula relates to the data you are describing--you didn't say what data is in what column--but I can explain what it's doing.

    =IF(D7="","",OFFSET($K$7,MATCH(D7,$J$7:$J$36,0)-1,0))

    Let's work it from the inside out.

    MATCH(D7,$J$7:$J$36,0)

    This looks for the value from D7 in the range of $J$7:$J$36. The 0 means "look for an exact match in an unsorted list." The result is the position of the value of D7 within the range $J$7:$J$36. For example, if the value in D7 is found in J8, the result is 2. For shorthand, let's call this <ValuePosition>.

    Next we have

    OFFSET($K$7,MATCH(D7,$J$7:$J$36,0)-1,0)

    or using our shorthand

    OFFSET($K$7,<ValuePosition>-1,0)

    This returns the value found in a position relative to a cell. The first argument says to start in $K$7, then move by <ValuePosition>-1 rows, and 0 columns. Because the range that we sought D7 in also starts in row 7, this expression finds the value in column K that is in the same row as where we found D7 in column J. With me so far? For shorthand, let's call this whole thing <ValueInK>.

    Now we finally have

    =IF(D7="","",OFFSET($K$7,MATCH(D7,$J$7:$J$36,0)-1,0))

    or using our shorthand,

    =IF(D7="","",<ValueInK>)

    We first test to see if D7 is a blank. If so, the final result is a blank. We have to do this, because next we're going to search for the value, and if we search for a blank we will likely produce an error.

    So if D7 is not blank, it is going to return the value in K that is in the same row where the value of D7 was found in column J.

    Now I will strongly suggest that you curl up with a glass of wine in front of a roaring fire and study the Excel help pages for OFFSET and MATCH.

    BTW I would have written this more simply as

    =IF(D7="","",VLOOKUP(D7,$J$7:$K$36,2,FALSE))

    which I leave as an exercise to the reader.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: need help understanding a formula

    First, the board strongly discourages quoting an entire post just to respond to it. It clutters the thread. You can edit the quote if there is a specific line or two you want to respond to.

    $J$7 and J7 refer to the same cell. But if I copy a formula with $J$7 to another cell, it stays $J$7. This is called an absolute reference.

    If I have a formula in A7 that references J7, and then copy it to A8, the reference will magically change to J8. If I copy to B9, it becomes K9. This called a relative reference, because Excel updates it to stay in the same relative position in the new cell as the old one.

    This is a very fundamental feature of Excel that is one of the first two or three things you learn when starting with Excel. That suggests you have some homework to do!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1