jgedwardsv
New Member
- Joined
- Oct 27, 2016
- Messages
- 31
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?
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?
Last edited: