Date format & Index function

Forestq

Active Member
Joined
May 9, 2010
Messages
482
Hi,

I have:

DateC17 = Jan FY13
NameC20 = Audi
OwnerE53 =

<tbody>
</tbody>

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.

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

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Try:

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

Forum statistics

Threads
1,216,116
Messages
6,128,933
Members
449,480
Latest member
yesitisasport

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top