Please see the attached.
I am using a function to find a value in the table based on the column date value in C22 and row time value in C23. Sometimes the correct value is returned (eg. if I enter 0:30 in C23) and sometimes N/A is returned (eg. 1:15).
Excel is saying that the Match part of the function is the problem. I have checked the formatting and the values seem to be the same. I have played around with the match function but cannot solve the problem. Hopefully someone can help. THanks in advance.
2 dimensional look up
-
-
-
Re: 2 dimensional look up
=INDEX($B$2:$AF$15,MATCH(C22,$A$2:$A$15,0),MATCH(C23,$B$1:$AF$1,1))
Rather than hard-code the ranges you should either convert the data into an Excel Table or create and use named ranges.
-
Re: 2 dimensional look up
Thanks for your reply.
I have expanded the table with the attached. Using the function you provided, with some times I am getting the incorrect value returned. The table values being returned for 12:00 are for 11:45. I changed the match_type for the time value from 1 to 0, which gave me a N/A value. I copied and pasted the 12:00 from the time row and this returned the correct value. For some reason when I type in the number the cells are'nt matching up. Again I checked the formatting with no issues. I also named the ranges as you said. Could you please have a look at the attached to see where I am going wrong. Thanks.
[ATTACH=CONFIG]51550[/ATTACH] -
Re: 2 dimensional look up
Excel 2010
Always use MRound() when dealing with time.
http://58918aa17d4002474361-7a…rackcdn.com/02_16_13.xlsx
If you get *.zip, don't unzip, just rename *.xlsx -
Re: 2 dimensional look up
Nice tip Herbds7 - was unaware of using MROUND with time values. Handy.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!