Find and Input with criteria

[gjergji]

WORK.xlsx

Hello I need a formula in sheet 1 cell b3 that will look up the product code from cell d3 in mrpoutdw column d and will find the date in mrpoutdw that is closest to but higher then the date on sheet 1 cell a3.

[tigeravatar]

gjergji,

Attached is a modified version of your posted workbook.
In 'Sheet1' cell B3 and copied down is this array formula. Note that array formulas must be confirmed with Ctrl+Shift+Enter and not just Enter. That's how the formula gets surrounded by the curly braces {}, do not try to add those yourself:

Please Login or Register  to view this content.

Then I clicked the Office Button -> Excel Options -> Advanced -> Scrolled to the section "Display options for this worksheet" and selected Sheet1 -> Unchecked "Show a zero in cells that have zero value" -> OK.

So any cells that didn't meet the criteria resulted in a 0. Turning off the display of 0 values hides that result. This could have also been accomplished with custom formatting or conditional formatting.

[gjergji]

this isn't working correctly for me it only shows the October dates.

[gjergji]

i know why in sheet mrpoutdw how did you change the dates to not have the green arrow on the corner

[gjergji]

hey thanks for the help i think i got it

[gjergji]

WORK.xlsx Hey I saw that there are a couple of days missing i high lighted them in yellow.

[gjergji]

forget it i forgot those are the less then dates thanks

[tigeravatar]

Set Excel to Automatic calculation instead of Manual
Converted all dates in sheet 'mrpoutdw' column F to actual dates
Unchecked worksheet option for 'Sheet1' so that it does not display 0 as a value in cells (so that they appear blank)

Used the same array formula in 'Sheet1' cell B3 and copied down:

Please Login or Register  to view this content.

Verified by hand several of the results, I can confirm that it is working properly. What is giving you pause?

[tigeravatar]

forget it i forgot those are the less then dates thanks

Oh ok. Glad to hear you got it sorted

[Cutter]

@ gjergji

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.