INDEX/MATCH formula with INDEX return being in row higher than MATCH value (xlUP)

[bifjamod]

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:

=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.}

[p45cal]

Consider putting a formula in column E to fill the blank cells with the value directly above them:
Select the column of data and blanks in column E.
Press F5 on the keyboard, click Special…, choose Blanks, click OK.
Without clicking on anything with the mouse, nor moving the cursor to disturb the currently selected cells, press the = sign on the keyboard, followed by tthe up arrow (cursor key), then hold down the Ctrl key and press Enter on the keyboard.
This puts a formula in all the blank cells.
If you want you can select the column of data again and copy, paste-special, values to convert those formulae into plain values.

Then you can use a simple vlookup formula on the lines of:
=vlookup("VALUE 4",$A$1:$E$500,5,FALSE)