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!!

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

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

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

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

That works awesome, thanks so much!!

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.