• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using INDEX and MATCH where lookup data is stored in a table

R

rpalarea

New Member
Excel 2007

Task: Retrieving values stored in a table where the left column and header contain values to match and the intersection of the header and left column is the value to return.


This is a pretty simple INDEX, MATCH that works quite well to lookup values at the intersection of a column value and row header, however I learned today of a possible shortcoming of Excel 2007 when trying to use the formula in Excel Tables; the header row that holds one of the critical matching values to find does not work when formatted as a Table. Even though Excel allows you to right click and set or ensure the format of the cell is a number, the nature of a Table header in 2007 is to store the value as text.


The elegance of using Tables to reference data in 2007 is much preferred for the simplicity of writing lookups and other strings of formulas. In this case, the lookup data had to be converted to a range first for my formula to work.


I discovered this because I knew I had the formula correct, but kept getting #N/A errors. When I converted the lookup table to a range, I could see that the numbers in the table header row were stored as text. I converted to a number and the formula worked.


I then converted the lookup range back to a table and the formula broke.


Good to know.
 
I don't think it is a short coming though. I just don't understand why one would retrieve info from the header row but not the body of the table. When you put together a index+match the majority of the time you are searching for a specific data in the body of the table.


Why would we need to find "critical match value" on the header row? If one doesn't know which column he/she is collecting data from, then he/she shouldn't be working on that data table.


I am not criticizing. Just don't understand the whole picture of it. that's all.
 
Fred, to do a double lookup, thats why.

Say you have a table with Month across the top and Product down the side, using this you can retrieve a value where Month = X and Product = Y, and X & Y are user supplied and You don't know what column or Row X & Y are in as they may change.
 
Hui - correct.


The table is setup to host shipping rates where the shipping zone is across the top (header) and the weights are in the leftmost column. A package going to zone 7 that weighs 11 lbs has a shipping rate in the cell intersection of those two lookup values.
 
Hi,


This is the second problem with Tables, I have a problem with he fact that you cannot have absolute references when referring to Tables.


Chandoo did a post on this problem.


It is a pity though, Tables are very useful.
 
My solution to this problem was to create a second "header" row that is part of the table. Then you can do an Index-Match lookup without an issue.


http://bit.ly/nTP1JF


Hope this helps.
 
You must log in or register to reply here.
Back
Top