Vlookup if condition applies

[Gustavo.AL]

Hi all,

I'm trying without success to find a formula that give me a value (code) in another sheet (check), if it is the first that happened after.

excel1.JPG
excel2.JPG

I've used a VLOOKUP at first, my problem is that the value code will appear randonly on different times, with different "Check ID", but my Vlookup will aways show the "Check ID" from the first row that have it, I've included a column with the time converted to numbers to make the formula easier, so what I need is to put a formula that find the "Check ID" with the smaller "time in numbers" that is bigger than the one in the current row.

[AliGW]

Welcome to the forum!

What exactly are you trying to achieve?

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Gustavo.AL]

Hi AliGW,

Thanks a lot for the anwser and info, the sample is attached now, the data is real except by confidential data that I already removed.

I'll examplify to make easier to understent:

I work in a warehouse with a very basic system, every time that a picker go to a place and don't find the item that should be there, it generate a check identified by a numeric sequence (sheet Check - Column D) to confirm if it was there or not, what I need to do is find on sheet Check this specific check id attached to that location and item, wich is represented by a code (Sheet Issue - Column A).

It would be easy to achieve with a vlookup to find the same code from sheet Issue on the sheet Check, the problem is that the same location (code) can have different checks in different moments (never at same time), so the function need to return the check ID in the same row from the same code that happened in the nearest moment after.

I've highlighted the first 8 rows were I didn't get the desired results in orange.

[JeteMc]

I'm not sure that I understand the logic. It would seem that the formula should find
1) matching codes
2) the first timestamp in column E on the check sheet that is larger than the time stamp in column F of the issue sheet.
An array entered formula* that accomplishes that, and in fact produces the desired check ID for 7 of the 8 identified (orange) rows is:

Please Login or Register  to view this content.

The problem is that while it corrects 7 of the 8 issues (row 53, N/A expected, being the exception) it creates 26 more cases where the check ID (column D) doesn't match the desire check ID (column E). Checking a few of those results the formula produces the correct check ID providing my understanding of the logic, listed above, is correct.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
If you would like to attempt to understand where the formula results differ then I suggest selecting cell D2 on the issue sheet,
Pasting the above formula into the formula bar,
Simultaneously pressing the Ctrl, Shift and Enter keys,
Double clicking the fill handle.
Then scroll down to row184, which is the first instance that the formula returns a different code from a previously given (not N/A) desired result. Perhaps inspecting why the formula's result is different from the desired results will help in defining the conditions for the formula.
Let us know if you have any questions.