What is the maximum criterias for Index Match function

[rkspeaks]

Good Afternoon All,


I have a problem in using the index/match formula. Normally it shoots like a Gun. But here I have four criteria in my problem. I got a #N/A error. I have gone thru some of Chandoo's index/match posts. Still I am not clear on one thing i.e. Is there any limit for the no of criteria to be used in the index match formula, when using multiple criteria.


From the following example, I want to get Cost, Tax, On Road Price for the products of an Automobile showroom.


http://hotfile.com/dl/177867748/bc05ae3/IndexMatch.xlsx.html


Please help me on this!


Thank you.


RK

 

[NARAYANK991]

Hi RK ,


Your formula is correct ; the data you are looking for is not present !


There is no automobile in your database matching the 4 criteria you have used viz. Tata , Hatchback , Drum , 4W.


At present your data validation drop-downs are independent , so that when the first one is selected , the choices in the 2nd , 3rd and 4th do not change accordingly. Hence the chances of choosing the wrong combination are always present.


Make the following selections in your drop-downs , and you will get a valid result : Porsche , HatchBack , Drum , 2W


Narayan

 

[sdsurzh]

Hi,


As already said by Excel Hero Narayan. your formula is correct apart from the below (TAX) and also can you please download the below file and check i have modified the data to get the output.


=INDEX($F$2:$F$153,MATCH(I2&J2&K2&L2,$A$2:$A$153&$B$2:$B$153&$C$2:$C$153&$D$2:$D$153,0))


http://www.2shared.com/file/-jSFFhs3/MI_online.html


Thanks,

Suresh Kumar S

 

[rkspeaks]

Oh! What a silly mistake..fine me for eating your head..


Anyways it's just working fine.


I cannot spend a day without meeting you guys...! :D


Thank you


RK