Originally Posted by
6StringJazzer
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.
Bookmarks