+ Reply to Thread
Results 1 to 27 of 27

Address Match and Indirect formula

  1. #1
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Address Match and Indirect formula

    Dear all

    I would like to extract some data and develop a formula in DNO2 cell to drag left and right to extract the value. But it does not provide any value.

    Please provide your valuable feedback.

    Regards
    Shams
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hi

    Please help me. I desperately need your help.

    Regards
    Shams

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    Great timing on the "bump" !
    I'll take a look, but no guarantees
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    it seems your announcement dates (column B) are not dates, so no possibility of a match

  5. #5
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hi Dreadwolf

    Thank you for your email. Waiting for your response.

    Regards
    Shams

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    see post #4

  7. #7
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hello

    How to convert them into date formate. Should it be worked =IFERROR(INDIRECT(ADDRESS((MATCH($B2,$D$1:$DEG$1,0)+DNO$2),3,1,1)),"")

  8. #8
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hi I have developed this formula for DNP2 cell. =IFERROR(INDIRECT(ADDRESS($DEH2,(MATCH($B2,$1:$1,0)+DNP$1),1,1)),""). But still it does not retrieve the value. DEH2 is the numeric value 2. (New column of value)

  9. #9
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Dear all

    I think the main problem is B2 date cell which is different format than the actual date in $1:$! row.

    Thanks
    Shams

  10. #10
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hi All

    =IFERROR(INDIRECT(ADDRESS($DEH2,(MATCH($B2,$1:$1,0)+DNP$1),1,1)),""). This formula works but the only problem is B2 date format. If I changed the date in B2 cell manually, it works. Please advice me how to convert the B column date into standard date format.

    Regards
    Shams

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    I think you are missing the point, the numbers in column B are translating intonegative dates,which excel does not handle well, the third date is outside of the allowable range for dates, the other 2 look like a concatenation of cells to reach what looks like a date, then used a value to get a number, but is not a valid date, not sure how these numbers were derived, but they are not valid dates, so your match can never give a value... sorry

  12. #12
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    There is no "esy" solution, this looks like an amalgram of formulus came up with the dates..... in first 2 you could use text functions, but the third looks like it was derived differently, so I have no "simple" way to correct them...

  13. #13
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    Assuming all the data is derived the same way, this MIGHT work :
    in C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    drag down


    EDIT-
    mistake -5 instead of -6

    no guarantees on this, but seems to work for the sample
    Last edited by dredwolf; 11-19-2013 at 01:09 AM.

  14. #14
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    if you show me the formula that gives you the non-date number, we could probably give you the formula that would make it an actual date, it seems redundant to me to turn a date into a string, then turn it back into a number to use the date..., just my opinion though

  15. #15
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    hI dREW tHIS FORMULA IN hrr2 DOES NOT PICK THE RIGHT VALUE YET
    Attached Files Attached Files
    Last edited by mahershams; 11-19-2013 at 02:03 AM. Reason: new file

  16. #16
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    @ mahershams, ah, so column B has "magically" been fixed now...interesting ...

  17. #17
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hi Dredwolf

    You are great. You have done a great job to convert this impossible to possible. I am very happy. But I do not know why my formula does not capture the right figure yet.

    I WILL ATTACH THE FILE FOR YOUR LOOK.

  18. #18
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hi Drew

    This formula in HRR2 cell does not pick the right value yet. Would you be able to have a look in HRR2 cell where it goes wrong now.

    Regards
    Shams

  19. #19
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hi Drew still the formula in HRR2 cell does not work. It retrieve some wrong values. Please have a look if possible.

    THANK YOU VERY MUCH FOR YOUR SUPPORT AND TIME.

  20. #20
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    I don't really need the file, just need to se how the value in column B is derived (ie the formula for column B0, if we can get that to be an actual date, then the rest is pretty simple, or use the C column conversion I already supplied and use that, I just think it's wasting time, memory, storage space to have re-calculate the date when it already exists...

  21. #21
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    I use the new conversion date in my new formula and it captures some value. But the value was not correct. The value should be 0.052632 in DOT2 should be in HRS2. But HRS2 shows 0.0190 this value. How is this happening.

  22. #22
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    Also it seems you are answering another poster, yet i see no posts from a drew, maybe PM's?

  23. #23
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    I feel like I'm in the middle of 3 different conversations here...

  24. #24
    Forum Contributor
    Join Date
    11-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    225

    Re: Address Match and Indirect formula

    Hi Drew

    I also lost where I am posting what. However good thing is that I have solved the problem. It now works to retrieve the data

    =IFERROR(INDIRECT(ADDRESS($DEF3,(MATCH($B3,$1:$1,0)+DMK$1),1,1)),"")

    I would like to tell you my deepest thanks for your support.

  25. #25
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Address Match and Indirect formula

    Good day Mahersham

    Moderators Note:

    Are you pertaining to the same person
    Hi Drew
    and
    Hi Dredwolf
    .
    If not please refrain from discussing via PM on this and other thread that will be created so not to create confusions on the part of those who are trying to solve your problem and those who are following the thread.

    Thanks.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  26. #26
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    Umm...okay, your welcome !
    Not really sure what happened, but if your happy, it is all good !

  27. #27
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Address Match and Indirect formula

    @ vlady, thank you!
    still not sure if it was me or another member..lol

    EDIT-
    The reason for my confusion is that I know we have a member named Drew who is very knowledgeable about Excel, so it throws me off if your calling me Drew...
    Last edited by dredwolf; 11-19-2013 at 02:35 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Indirect Address Match - search value based of 2 variables
    By arazoe in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-09-2010, 06:04 PM
  2. Indirect address/match
    By Prcntrygrl in forum Excel General
    Replies: 4
    Last Post: 08-26-2009, 05:05 PM
  3. Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 12:05 PM
  4. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Andy Wiggins in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions
    By Mike Barlow in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1