MATCH #N/A Error

[jgedwardsv]

Hi all,

I've been trying to do a two dimensional lookup in excel and am having trouble with the match formula.

I have two columns in the main table, one which will be the row lookup in another table and the other that is the headers of the same table. The first one is a percentage rounded to two decimal points. The second (where the match function is supposed to be looking up) is a whole number. There are exact matches for the whole number in the headers of the table, yet match still returns #N/A error (I've looked in the calculation steps and it does return #N/A).

The numbers I am looking up are 2000, 2005, 2010, 2015, 2020, 2025, 2030, 2035, 2040, 2045, 2050, 2055, 2060, 2065. Those are the only numbers that would be returned in the column and they are the only numbers in the header as well. I can attached the workbook sample if it helps.

I have checked the data types as well - the data types are all formatted as numbers and all have 0 decimal places shown. I have tried the -1, 0, and 1 arguments for match and the result is still the same. Am I missing something here?

John

edit: The full formula I am using is =VLOOKUP([@ReplaceBlended],TDFEq,MATCH([@TDFSeries],TDFEq[#Headers],0),TRUE) where [@ReplaceBlended] is one column for the percentage, TDFEq is the table I am referencing, [@TDFSeries] is the second column, TDFEq[#Headers] are the headers for the other table.

In that table, the first column is formatted as a number as well, however, the contents are text. Would that make a difference?

 

[RicardoCubed]

Please post sample. I have struggle with same and perhaps can help.

 

[jgedwardsv]

https://www.dropbox.com/s/ddptn98no1b1kgs/TDFWorksheetTemplate.xlsx?dl=0

That's the current version of the workbook.

In the future I will use specific participant data, however, to make it work I randomized everything while testing the output, etc.

 

[philaugust2004]

From what I can see the error appears to be because the years in TDFconstants in A6 to O6 are numbers shown as text rather than actual numbers

 

[jgedwardsv]

How do I fix it so they are actually numbers and not numbers shown as text?

 

[philaugust2004]

There seems to be some sort of format protection. If you edit one of the cells and multiply by 1 it says 0. Even formatting as number in the usual way doesn't work

 

[jgedwardsv]

I tried a couple of methods to get the numbers to be numbers and despite these attempts it still doesn't work. Would I be better off remaking the table and starting from scratch?

 

[philaugust2004]

Maybe wait for other answers first as someone will probably have a solution and I may even be on wrong track

 

[jgedwardsv]

Sounds good to me.

I'll wait patiently. The rest of the analysis I want to create is dependant upon being able to get something into that column. The next step would be to average out all the results based on the group they fall into and then compare to the equity allocation for the target date fund series in another table.

 

[wideboydixon]

Agreed that it's a numbers/text mismatch. However, you might be able to use this instead:

=VLOOKUP([@ReplaceBlended],TDFEq,MATCH(TEXT([@TDFSeries],"@"),TDFEq[#Headers],0),TRUE)

WBD