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...

	A​	B​	C​	D​	E​	F​	G​
1​	Date	Blah1	Blah2		1/8/2017​	Blah1	Blah2
2​	1/1/2017​	10​	100​			50​	500​
3​	1/1/2017​	20​	200​			60​	600​
4​	1/1/2017​	30​	300​			70​	700​
5​	1/1/2017​	40​	400​			80​	800​
6​	1/8/2017​	50​	500​
7​	1/8/2017​	60​	600​
8​	1/8/2017​	70​	700​
9​	1/8/2017​	80​	800​
10​	1/15/2017​	90​	900​
11​	1/15/2017​	100​	1000​
12​	1/15/2017​	110​	1100​
13​	1/15/2017​	120​	1200​
14​	1/22/2017​	130​	1300​
15​	1/22/2017​	140​	1400​

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