Lookup of some kind need an expert

[Blueorangez]

Hi All

Hopefully someone can help me.

I have a Tab called "Detail"

The dates week ending, are in Column E Detail tab.



I need to do a lookup from another tab that returns the data in Column N though to column V "N:V" on the "detail" tab
if the date is = to the date I type in a random cell, lets say A1

I've tried a Vlookup but I discovered that a Vlookup only returns the first date it finds in the table.

The reason I'm struggling is because the dates are weekending, so there are multiple of the same date.
I would like to return all the results for that week ending.

The dates in column E represent failures there could be as many as 15 failures a week


so I would like to type 08/oct/17 in cell A1 and have a table 15 rows down by 9 rows across return my data from N:V in the Detail Tab. it will have to return all results that match weekending 08/oct/17

if there's only 6 results, only display 6 results and leave the rest of the rows in the table blank.


Thankssss

 

[FDibbins]

See if you can adapt this to your needs...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td]Date[/td][td]Blah1[/td][td]Blah2[/td][td][/td][td]

1/8/2017​

[/td][td]Blah1[/td][td]Blah2[/td][/tr]

[tr][td]

2​

[/td][td]

1/1/2017​

[/td][td]

10​

[/td][td]

100​

[/td][td][/td][td][/td][td]

50​

[/td][td]

500​

[/td][/tr]

[tr][td]

3​

[/td][td]

1/1/2017​

[/td][td]

20​

[/td][td]

200​

[/td][td][/td][td][/td][td]

60​

[/td][td]

600​

[/td][/tr]

[tr][td]

4​

[/td][td]

1/1/2017​

[/td][td]

30​

[/td][td]

300​

[/td][td][/td][td][/td][td]

70​

[/td][td]

700​

[/td][/tr]

[tr][td]

5​

[/td][td]

1/1/2017​

[/td][td]

40​

[/td][td]

400​

[/td][td][/td][td][/td][td]

80​

[/td][td]

800​

[/td][/tr]

[tr][td]

6​

[/td][td]

1/8/2017​

[/td][td]

50​

[/td][td]

500​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

1/8/2017​

[/td][td]

60​

[/td][td]

600​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]

1/8/2017​

[/td][td]

70​

[/td][td]

700​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]

1/8/2017​

[/td][td]

80​

[/td][td]

800​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]

1/15/2017​

[/td][td]

90​

[/td][td]

900​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]

1/15/2017​

[/td][td]

100​

[/td][td]

1000​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

1/15/2017​

[/td][td]

110​

[/td][td]

1100​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

13​

[/td][td]

1/15/2017​

[/td][td]

120​

[/td][td]

1200​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

14​

[/td][td]

1/22/2017​

[/td][td]

130​

[/td][td]

1300​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

15​

[/td][td]

1/22/2017​

[/td][td]

140​

[/td][td]

1400​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
F2=IFERROR(INDEX(B:B,SMALL(IF($A$2:$A$15=$E$1,ROW($A$2:$A$15)),ROWS($A$1:A1))),"")
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try to enter the {} manually yourself.
copy don and then copy across as needed

 

[C with no eyes]

It needs a helper column.

Insert a new column left of column N. (Your column N becomes column M)
I assume your data starts in M2 to leave space for a heading so in the new N2 type
=M2&countif($M$1:M1,M1)

This will give a unique reference to the data in column M

when you VLOOKUP on the next sheet make your lookup values $A$1&1 for the first row, $A$1&2 for the second row

Or if, like me, you'd rather have something that can be dragged if necessary make your lookup values $A$1&ROW() if you enter it on row 1 first (or $A$1&ROW()-4 if you enter it on row 5 etc.).

 

[FDibbins]

@ C with

1. you could put the helper on the far right and hide it
2. If you insert a column to the left of N, that new column is M, and N becomes O, not M
3. Your COUNTIF() will work OK, but not really sure you would include the M2& part?
4. Any time you include & in a formula, like you did, the result is always text and no longer a value, so not really sure that will be usable as a row counter in a vlookup?
5. to create an incremental list (like for what you are doing), this will work better...
=ROWS($A$1:A1)
copied down

 

[C with no eyes]

1. you could put the helper on the far right and hide it. Yes, I meant to say to hide it but I obviously forgot.
2. If you insert a column to the left of N, that new column is M, and N becomes O, not M You are correct in saying that N become O but the new column will be N.
3. Your COUNTIF() will work OK, but not really sure you would include the M2& part? Yes, the values need to be 100% unique in order for the vlookup to work.
4. Any time you include & in a formula, like you did, the result is always text and no longer a value, so not really sure that will be usable as a row counter in a vlookup? It works, try it.
5. to create an incremental list (like for what you are doing), this will work better...
=ROWS($A$1:A1) But we're not after an incremental list. It needs to be a unique reference that also includes the week end date
copied down

My comments are in bold.

Change anywhere I referred to M in my first post as O.

 

[Blueorangez]

Thanks guys

I dont really have control over the source file as its sent daily. I managed to adapt your array fx, works perfectly.

thanks for your help.

 

[FDibbins]

Happy to help