Retrieve Value of Last Non-Empty Cell in a range prior to given value

Hi All,

I am trying to create a form that updates its values dynamically based on a user-entered date. This form pulls data from a range in another sheet that have dates entered as the column headings, like so:

8/1/2019	9/1/2019	10/1/2019	11/1/2019	12/1/2019	1/1/2020
85	75	100
1000		1500		2000	2500
300			500	600

So what I am trying to do, is take a given date (I'm going to use 11/1/2019 in this example) and return the value in a specific row under that date. Easy HLOOKUP function, no problem. However, if there is no entry for that date in my table, what I want to do is pull the last non-blank cell in the row prior to the column that corresponds to the target date. Using the above table, what I would am trying to do would result in:

Target Date	11/1/2019
Value	Date of Entry
100	10/1/2019
1500	10/1/2019
500	11/1/2019

Where target date is a user-entered value. So if I change the target date to 12/1/2019, the values would update to 100, 2000, and 600. What I have so far is an IF statement that first runs HLOOKUP to see if there is a value in the proper row for the target date, returns it if true, and if false it grabs the last non-empty cell in the row:
=IF(HLOOKUP(B1,Sheet1!A1:F4,2,FALSE),HLOOKUP(B1,Sheet1!A1:F4,2,FALSE),INDEX(Sheet1!A1:F4,MATCH(9.99E+307,Sheet1!A1:F4))

That probably looks like a mess, but it does almost everything I want it to do. The problem is created by rows that have values *after* the target date - my index/match function uses the entire table as its array, so it doesn't know when to stop and returns values too far down the table. How do I adjust this function so that it only looks for values in the range A:{column indicated by target date}?

Thanks everybody!

I think what you are saying is that you want to change the F4 which occurs twice in the INDEX/MATCH function needs to be dynamic to respond to the user-entered date. You can do that using INDIRECT or another INDEX function.

It's difficult to give you an exact formula to try, as I suspect from the formula that you quoted that the two tables are on different sheets, so it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Pete

Thank you!! I was finally able to circle back around to this and have it working exactly as desired. I really appreciate the help!

Just out of curiosity, what exactly is happening in the LARGE function? Why is the column row divided over itself and then again over the target row?