+ Reply to Thread
Results 1 to 8 of 8

Index/Match Formula Issue

  1. #1
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    143

    Index/Match Formula Issue

    Hi,

    I can't seem to make the index formula work properly using anything but "0" (exact value). I need to lookup the value in cel A5 & return the right value from column E on the 2nd tab. Thanks!!
    Attached Files Attached Files

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Index/Match Formula Issue

    The 'Age' in A5 is a date, not an actual number, it won't match that in the range, so it takes the next highest.
    1. Change the format for cell A5 to 'general' then change the number to 32
    2. Change your formula to this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hope that helps!

    -Moo
    Last edited by Moo the Dog; 11-23-2012 at 03:58 PM.

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Index/Match Formula Issue

    You may have noticed the +1 after your first match statement... this is because 'Age_Table' starts in row 3, but ''Age_Table_Ages' doesn't start until row 4, so you have to add a row to return the correct value.

    - Moo

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Index/Match Formula Issue

    A couple of things were wrong - Cell A5 actually contains a date (20th Nov 1932) and is formatted to show only the year, so you might think it contains the number 32 but it really contains the number 12013 (which is the serial number for that date - the number of elapsed days since the reference of 1st Jan 1900), and so it was returning the values for 70+ from your table. So to correct this, you need to format that cell as General or Number with 0 dp, and then put the number 32 in it. You might also like to apply Data Validation to it so that you can only input numbers in a reasonable range.

    The second problem is that you have defined the named range Age_table to include the header row, so you should change this to refer to 'Table 2-2'!$A$4:$E$14 using Name Manager.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    143

    Re: Index/Match Formula Issue

    Thank you both, I understand that now, and have got it fixed .
    That still leaves me with a bit of a problem though...

    The date in cell B2 is actually a date. It is looking up from another tab in my real file how old an employee is, which is figured out using Jan 1, 2013 - his actual birthdate = years old (32). So I guess I need to look up that cell & have it return an actual # of 32. The only way I know how to do that is using the "text" command, but that doesn't work because then it's a text value instead of a #.

    I've reattached the file with the changes, thanks
    Attached Files Attached Files

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Index/Match Formula Issue

    Change cell E3 on the EE Ages... sheet to a 'General' format, and use this formula in that cell: =DATEDIF(D3,$H$1,"y")

    Then the lookups on the Group Term.. sheet should work fine.

    - Moo

  7. #7
    Forum Contributor
    Join Date
    06-19-2012
    Location
    Canada
    MS-Off Ver
    Office 365
    Posts
    143

    Re: Index/Match Formula Issue

    That works awesome, thanks so much!!

  8. #8
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Index/Match Formula Issue

    You're welcome. Glad I could help.

    - Moo

    - - - - - - - - - -
    If you are satisfied with the solution(s) provided, please mark the thread as [SOLVED] by clicking on the words 'Thread Tools' above your 1st post in this thread. Then select 'Mark Thread as Solved'.

    Also, a good way to 'Say thanks' to those who have helped is to click on the star below their name in one of their posts. It's always appreciated.

+ 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