Vlookup results based on IF Function

[numbersdntlie]

Hi all. In the attached worksheet, I am trying to get the dates from Column K into column B for each row to the left of the yellow divider if it the criteria of C=M,E=J,L=F is met for a certain. I attemped such a function but get the FALSE Value. I'm guessing it's looking for a match from left to right instead of scanning the columns to find a criteria that matches. The orange highlight indicates what I would like to happen. Please see row 456 (highlighted orange) and row 8168 (highlighted in orange). The date from column k is in column b (highlighted in green) based on the items in orange being the same. I would like to have a formula that can scan and compare what's on the left and right of the yellow divider and import the date to column b from k if there is an exact match based on the 3 criterias set.

[NBVC]

Invalid attachment... try again, making sure the .xls or .xlsx ending is in tact.

[numbersdntlie]

Sorry, I pulled it to make some mods, it should be visible now.

[NBVC]

I think it's:

=IF(AND(C2=M2,E2=J2,F2=L2),K2,"")

but I am not seeing any matches...

[numbersdntlie]

There should be a match from the items I highlighted in orange. Also, in your formula isn't a vlookup function required? I think it's not finding anything because "=IF(AND(C2=M2,E2=J2,F2=L2),K2,"")" is only restricted to those particular cells, instead of searching for the exact cominations in differents rows throughout the worksheet between the two dividers.

[NBVC]

You can use:

=IFERROR(INDEX($K$1:$K$10000,MATCH(1,INDEX(($M$1:$M$10000=C2)*($J$1:$J$10000=E2)*($L$1:$L$10000=F2),0),0)),"")

but it may be a bit slow.

If you can convert the dates in column K to actual dates... or add a helper in column N with formula: =DATE(RIGHT(K2,4),LEFT(K2,2),MID(K2,3,2)) formatted as Date, to do it for you , then you can use SUMIFS (assuming only one match will occur)....

=SUMIFS(N:N,M:M,C2,J:J,E2,L:L,F2) and format column as custom: m/d/yy;;;@ to get rid of 0's

[numbersdntlie]

Do I put this in front of the vlookup function or is this the only function I need and it will input the date from the other column?

thanks for your help on this.

[numbersdntlie]

I get some dates in there with your formula, but my given example on row 456 doesn't populate a date when I put the formula in there.

[NBVC]

I got 12012011 with first formula and 12/01/2011 with second formula in that cell

[numbersdntlie]

Great, thank you so much.