Index formula and the duplication in the other file

Ali Alrubaye

Hi Dear

I have use the index formula the to pull the data from file 2 to file 1 , its work , however in some cases I have under the same employee name in (file 2 ) two different submission date , in my formula I pull only the first submission date from file 2 to file one ,And now I need your help to create other formula to pull the other submission date in another column in file 1 ,

Note: I have only two different submission date in file 2 no more so I think we can first choose the earlier date and then we can choose the latest date.

Jason Tarpinian

If you use INDEX(COLLECT()) you can choose which value you'd like to return, as COLLECT will filter a range down for you, then you use the INDEX function to choose which row you'd like to return.

Using your ranges above, change your formula to below, with N being the nth value you'd like to return (so 1 would return 4/4/24, and 2 would return 5/2/24)

=INDEX(COLLECT({img_sub}, {img_name}, Name@row), N)

Ali Alrubaye

Thank you for your reply Jason Tarpinian , But in the photo this is only example as I have many names have two time submission date So in my formula its chose the first raw if duplicated and I want other formula to show the second raw of submission date in another cell in the file one.

Jason Tarpinian

If I'm understanding correctly, that's what the "N" in the formula is for. In my example below, the first formula pulls in the first match for Employee name (in red), using 1 for the index row. For the 2nd, in green, it pulls the second value. You can continue this for as many as you'd need, or use MAX/MIN if that makes more sense. Or you can use the JOIN function to show all dates within a collected range at once CHAR(10) is a line break to see all dates in a single cell.

Ali Alrubaye

Many thanks for you Jason Tarpinian , The issue is solved ,you are the hero