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?

 

[jgedwardsv]

Unfortunately that doesn't work either...

How can I correct the mismatch on the table headers?

 

[wideboydixon]

Strange. I downloaded your sheet and changed the formula to be exactly that and it retrieved values. I think it's probably related to having these numbers as column headers in a table that is the source of your problem.

WBD

 

[philaugust2004]

That formula worked for me too on the downloaded file

 

[jgedwardsv]

Must have been a function of me playing with the original. I pulled down the one I put in the dropbox and it worked for me. Thanks for your help guys!