INDEX/MATCH formula with INDEX return being in row higher than MATCH value (xlUP)
I am using index/match formula to locate a value in a spreadsheet (column A), then return the corresponding value in column E. However, due to the structure of the document, the value needed in column E is not in the same row as the matched value. It can be in the row immediately above (in which case adding an OFFSET would solve the problem), or it could be 5 or 10 rows above the match value row (it varies by record, so offset is not feasible). If I were processing this in VBA, I believe End.xlUp functionality would solve this for me.
| A |
B |
C |
D |
E |
| VALUE1 |
X |
Y |
Z |
9.9 |
| VALUE2 |
X |
Y |
Z |
|
| VALUE3 |
X |
Y |
Z |
|
| VALUE4 |
X |
Y |
Z |
|
In the example table, my match value is column A, and let's say I'm matching VALUE4. I need to then return the value of column E. In this case, the value that applies is 3 rows above (9.9), corresponding to VALUE1 (which is otherwise irrelevant.) Therefore, what I need is the formula to find VALUE4, offset 4 columns to COLUMN E, then find the first cell ABOVE that row which contains a value, and return that value.
I hope this is not to confusing, and I can provide the sample worksheet I'm working with if needed.
If anyone can help with my formula, I'd appreciate it.
One experiment I tried, that returns an #N/A error, is this:
Quote:
=LOOKUP(2,1/(INDEX($E:$E,MATCH("VALUE4",$A:$A,0))<>""),INDEX($E:$E,MATCH("VALUE4",$A:$A ,0)))
Note - there may be errors in this formula due to the simplification of it from the actual formula I am using.
