+ Reply to Thread
Results 1 to 5 of 5

lookup? Index? match? formula

  1. #1
    Richard
    Guest

    lookup? Index? match? formula

    I have this formula in cell B17 =DATEDIF(B16,TODAY(),"Y")&" years,
    "&DATEDIF(B16,TODAY(),"YM")&" months "&DATEDIF(B16,TODAY(),"MD")&" days" This
    gives me the total years of employment. I need to take that info and match it
    with column "I" then whatever is in column "J" send that to "A3"
    Crude Example: If B17 = whats in column "I5" then send whats in column "J5"
    to "A3"
    B17 shows the years, months and days but column "I" only shows whole numbers
    like 1,2,3,4, etc...can they still match for example: B17 = 7 years, 2 months
    5 days, I need that to match with 7 years in column "I"
    Thanks in Advance!! Tough one for me!

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    If column I shows just numbers, e.g. 7 (not "7 years")

    In A3

    =VLOOKUP(DATEDIF(B16,TODAY(),"Y"),I:J,2,0)

  3. #3
    Richard
    Guest

    Re: lookup? Index? match? formula

    Works Great, can it be modified to show 25 in A3 if B16 is greater than 30.

    "daddylonglegs" wrote:

    >
    > If column I shows just numbers, e.g. 7 (not "7 years")
    >
    > In A3
    >
    > =VLOOKUP(DATEDIF(B16,TODAY(),"Y"),I:J,2,0)
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=515134
    >
    >


  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Isn't B16 a date?, do you mean if DATEDIF(B16,TODAY(),"Y") is bigger than 30? Possibly

    =IF(DATEDIF(B16,TODAY(),"Y")>30,25,VLOOKUP(DATEDIF(B16,TODAY(),"Y"),I:J,2,0))

  5. #5
    Richard
    Guest

    Re: lookup? Index? match? formula

    Yes, you're right! Thanks so much. It works perfect now!!!

    "daddylonglegs" wrote:

    >
    > Isn't B16 a date?, do you mean if DATEDIF(B16,TODAY(),"Y") is bigger
    > than 30? Possibly
    >
    > =IF(DATEDIF(B16,TODAY(),"Y")>30,25,VLOOKUP(DATEDIF(B16,TODAY(),"Y"),I:J,2,0))
    >
    >
    > --
    > daddylonglegs
    > ------------------------------------------------------------------------
    > daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
    > View this thread: http://www.excelforum.com/showthread...hreadid=515134
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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