Date format & Index function

[Forestq]

Hi,

I have:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]C17 = Jan FY13[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]C20 = Audi[/TD]
[/TR]
[TR]
[TD]Owner[/TD]
[TD]E53 = [/TD]
[/TR]
</tbody>[/TABLE]

C17 is like: Jan FY13, Feb FY13, Jun FY13, Apr FY13, May FY2014

In my source I have column with other format date (Sheet2, column B): DD-MMM-YYYY (31-May-2014 or 30-Apr-2014).

Now I want to use Match function to find owner.

=INDEX(('Sheet2'!B4:B1000=C17)*('Sheet2'!D4:D01000=C20)*('Sheet2'!BJ4:BJ1000))

How I need to write proper function to find owner??

 

[DILIPandey]

Hi Forestq,

Data layout is not clear to me.
Are you having Dates in C17 and downwards?
Sheet 2 column B is having dates but not names then how you will bring names if date in C17 matches anywhere in column B of sheet 2?


Regards,
DILIPandey

 

[Forestq]

in C17 I have value: Apr FY13 or Jan FY2014

sheet 2 is source data: column B contains dates with format dd-mmm-yyyy, in column D I have "Name" (like Audi).

 

[Redwolfx]

I'm not toally sure if this is what you are looking for.

I think Sheet2 Column D are the Audi type names and Sheet2 Column BJ Are Owner's Names?

With one extra Column Added

Sheet2 Column C
c4 =TEXT(B4,"dd-mmm-yyyy")

Then in Owner
e53 =INDEX(Sheet2!BJ4:BJ1000,MATCH(LEFT(C17,3)&RIGHT(C17,2)&C20,MID(Sheet2!C4:C1000,4,3)&RIGHT(Sheet2!C4:C1000,2)&Sheet2!E4:E1000,0),1)

This must be confirmed with Ctrl + Shift + Enter

 

[DILIPandey]

HI Forestq,

Would be helpful if you can copy paste directly from Excel to here in reply window so that data layout becomes clear. Thanks.


Regards,
DILIPandey

 

[Andrew Poulsom]

Try:

=INDEX(Sheet2!BJ4:BJ1000,MATCH(1,INDEX((Sheet2!B4:B1000=EOMONTH(SUBSTITUTE(C17,"FY",""),0))*(Sheet2!D4:D1000=C20),),FALSE))

 

[Forestq]

Thanks Andrew!

your formula works fine for me,

regards,
Pawel