I'm not sure what to call this thread. Search and return data?

[nineonline]

Hi All - Sample attached.

I have two worksheets within this workbook and on worksheet 1 (Index) I need a formula in cell C2 that searches the area A2:D6 in the TEST CLIENT worksheet for the last entry in column D (Actual Dates) and returns the text in column A.

Thank you in advance for any help or suggestions you may provide!

[arthurbr]

Hi
perhaps

Please Login or Register  to view this content.

entered as an array formula with Ctrl+Shift+Enter

[nineonline]

You are my personal HERO today arthurbr. Many many thanks to you! I hope you're safe and well.

[nineonline]

I've tested this and it works wonderfully but when a new date is added to the next step in the D6 cell on the TEST CLIENT worksheet the value on the INDEX page is not updated to reflect 'STEP 15' Not sure if I've done something wrong or if something isn't accounted for within the formula?

[nineonline]

I've tested this and it works wonderfully but when a new date is added to the next step in the D6 cell on the TEST CLIENT worksheet the value on the INDEX page is not updated to reflect 'STEP 15' Not sure if I've done something wrong or if something isn't accounted for within the formula?

I should note, if the date in cell D3 on the TEST CLIENT worksheet is removed this formula works when a new date is entered in any other cell in column D however I need to keep the data in the D2:D6 area as historical information so as to say the formula needs to return the Step number in column A based on the last date entered in column D

[arthurbr]

Then try

Please Login or Register  to view this content.

[nineonline]

That formula is returning a #N/A error

[nineonline]

Actually apologies this formula works provided the successive dates entered are increasing. So as to say the last date entered in Column D must be later than the one previously entered. So this works, thank you. My only issue left is copying this formula all the way down column C in the index page and having it work. Right now I manually have to enter the reference in column A in the first worksheet which is actually a hyperlink to the TEST CLIENT worksheet but I can try and google this - thanks so much for your help!

[arthurbr]

Actually apologies this formula works provided the successive dates entered are increasing

As you can see in the attached, this is not the case

[arthurbr]

My only issue left is copying this formula all the way down column C in the index page and having it work. Right now I manually have to enter the reference in column A in the first worksheet which is actually a hyperlink to the TEST CLIENT worksheet but I can try and google this - thanks so much for your help!

Perhaps add a sheet with these new requirements?

[nineonline]

Thank you for the sample but I see on the INDEX worksheet cell D2 is returning a value of Step 1. The last date entered on the TEST CLIENT worksheet is in cell D3 and therefore 'Step 5' should be returned in D2 of the INDEX worksheet.

In any case I've adjusted everything to work out. Thank you so much for all your assistance you've been very helpful!