=INDEX(Sheet2!C2:C21,MATCH(DATE(Sheet1!B1,Sheet1!A1,Sheet1!A2),Sheet2!A2:A21,0))
Please help me correct this formula, I get #VALUE error.
Sample workbook attached herewith.
Thank you
=INDEX(Sheet2!C2:C21,MATCH(DATE(Sheet1!B1,Sheet1!A1,Sheet1!A2),Sheet2!A2:A21,0))
Please help me correct this formula, I get #VALUE error.
Sample workbook attached herewith.
Thank you
Try this instead...
=INDEX(Sheet2!C2:C21,MATCH(DATEVALUE(C2&" "&A1&" "&B1),Sheet2!A2:A21,0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
If you are going to use the DATE function the text October must be coerced into its month number value. If you change formula toit returns USD which is correct ... but there are multiple Mon, 01/Oct/18 dates in 'Sheet2'. Match only finds the first matching matching date. Is that what you want?Formula:Please Login or Register to view this content.
Dave
try
=INDEX(Sheet2!$C$2:$C$21,MATCH((A2&$A$1&$B$1)+0,Sheet2!$A$2:$A$21,0))
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
cool, 3 completely different approaches
@ Samba
That is really cool. I've never seen nor considered that approach.
It's in my toolbox now.
Thanks
Thank you everyone! all 3 approaches work on the sample file, unfortunately couldn't
make it work on my actual file. I am very sure I am doing something wrong , and I
want to find the multiple entries not just the first one.
Please see the attached file, I hope my query is explained in detail, please let me know
if need any other info. Sorry, my English is not so good.
Thank you for your help.
Already you have Date in A3 which is formatted to display day only, so use below formula
=INDEX(CalTime,MATCH(A3,CalDate,0))
Thank you so much...this works, but I would like to get all data under that date
This proposal employs structured references.
The formula for A4:A17 is: =IFERROR(INDEX(Table1[Time],AGGREGATE(15,6,(ROW(Table1[Date])-1)/(Table1[[Date]:[Date]]=$A$3),ROW(1:1))),"")
The formulas for the other columns are similar.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks