Vlookup returning first row coloumn data and not comparing rest of coloumn

Hi all,

New member and first time posting!

I hope all is doing well.

Expected outcome:
To return a date greater than today but closest to todays date.

Situation:
I have a list of customer ID's in a tab. In another raw data tab I have a download of all customers and their contract details. If I filter a specific customer ID on the raw data tab e.g. 12345 it will return multiple lines with the same customer ID as the customer ID shows up more than once because it has multiple contracts.

There is a coloumn in the raw data tab that had contract start dates. I want to return the contract start date that is greater than today but out off all the contracts listed for the customer I need the one closest to starting after todays date.

Example: So If I do a VLOOKUP(ACCOUNT ID, RAW DATA SHEET, CONTRACT START DATE COLOUMN, COLOUMN #, FALSE) the return will be only the date in the first row.
I need the vlookup to look at all rows with that customer idea and provide me with the next contract starting after todays date.

I hope this makes sense.

Thank you for your help.

Hello and welcome to the forum.

Attach a sample workbook making sure there is just enough data to demonstrate your need.

Make sure your desired results based on the sample data are shown, mock them up manually if necessary.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Example added

Try this in C5 of the 'Expectation page' worksheet:

=MIN(IF(('Raw data download'!A$2:A$10=A5)*('Raw data download'!C$2:C$10>TODAY()),'Raw data download'!C$2:C$10)) Ctrl Shift Enter

If you want a formula to return columns A and B of the 'Expectation page' worksheet as well, you can use this in A5:

=INDEX('Raw data download'!A$2:A$10,MATCH(0,IF('Raw data download'!$C$2:$C$10>TODAY(),COUNTIF(A$4:A4,'Raw data download'!A$2:A$10),""),0)) Ctrl Shift Enter

Drag through B5 then down as far as needed.

Hi,

That does work for customer 12345, however this is based off referencing Row A5. When I do the full Raw data download I will not know which line item has the next inline date after today's date.


So from the raw data download I need to return Only 1 Customer ID, Customer Name, contract start date (After today's date)

I'm not sure what you mean. The formula in column C references to the Customer ID in column A.
You had your headers in row 4 in the sample from post #3 so the first row with a formula was row 5.

So from the raw data download I need to return Only 1 Customer ID, Customer Name, contract start date (After today's date)

That's exactly what the formulas that have been provided in posts #4 and 5 do.

Based on the sample from post #6 (which has headers in row 1), you can use these formulas:

A2 =IFERROR(INDEX('Raw data download'!A$2:A$10,MATCH(0,IF('Raw data download'!$C$2:$C$10>TODAY(),COUNTIF(A$1:A1,'Raw data download'!A$2:A$10),""),0)),"") Ctrl Shift Enter
Drag through B2

C2 =IFERROR(1/(1/MIN(IF(('Raw data download'!A$2:A$10=A2)*('Raw data download'!C$2:C$10>TODAY()),'Raw data download'!C$2:C$10))),"") Ctrl Shift Enter

Then drag all three formulas down as far as needed.

The only modification made (besides where to put the first formula) is to have the extra cells show as blanks.

See attachment. I have dragged the formulas down through row 10.

Yes, it absolutely works on the example as expected. I am having issues getting it to work on my actual sheet.

I will have to spend some more time with it and get back to you.

Thank you very much for the help thus far!

Hi,

OK, I got everything to work, but there is an issue. It appears to be looping...

My raw data sheet is around 3500 rows so I adjusted the formulas as shown here:

=IFERROR(INDEX(Data!A$2:A$5000,MATCH(0,IF(Data!$O$2:$O$5000>TODAY(),COUNTIF(A$1:A40,Data!A$2:A$5000),""),0)),"")
=IFERROR(INDEX(Data!B$2:B$5000,MATCH(0,IF(Data!$O$2:$O$5000>TODAY(),COUNTIF(B$1:B40,Data!B$2:B$5000),""),0)),"")
=IFERROR(1/(1/MIN(IF((Data!A$2:A$5000=A41)*(Data!O$2:O$5000>TODAY()),Data!O$2:O$5000))),"")


When I drag the formulas down, it gets to the point where it hits the end of my raw data sheet rows and then just restarts right back at the beginning returning all the same information.

If you can create a more representative sample of your data where the formulas from post #7 fail, I'll take a look.

I can't go off of a non-working formula only.

The formulas do work, and they return the correct info.

The only issue now, is that the formula continues to pull data back for rows that should be empty. Example. Raw data tab only goes to row 3500. If I pull the formula past row 3500 it will start to return the information at the beginning of the raw data, so it reverts back to A1 cell Customer ID and Customer name. Instead of just stopping and not returning anything.

If the formulas are not working as expected, they do not work.

Again, if you create a more representative sample of your data where the formulas from post #7 do not work as intended, we can go from there.

It is tough to make a more representative example as the sheet I use has confidential information and a lot of data.

I have attached an example, but it does work. However, is my normal sheet once I get past 300 returns out of the 3500 records it starts to miss match the customer number and the name.

Hi JeteMc - Sorry for the delay here. My laptop was not functioning properly so running any type of reports would cause it to crash. I will relook into this sheet now and see if I can get a more populated example back to the community.

Thanks!