Help me for Multiple Criteria for index function [SOLVED]

[abid.aries]

Dear Seniors,


I have a problem, I need a formula to lookup values on the basis of multiple criteria on my report. I am attaching data with report. kindly resolve the problem

Thanks in advance


https://www.dropbox.com/s/86zqcbrbtpi79x8/Gate%20Pass%20Details.xls


I have highlighted the areas, which have criteria and where I required the formula.

I need formula based on criteria that can get all the descriptions from the "data sheet" on my "Report Sheet" and with the same Gate Pass Number "GP" I have auto generated an Internal Gate Pass No "IGP" for ease.


In short a formula is required for my "report sheet" based on Gate Pass No that can auto show all the description of the Gate Pass, no matter how many things are on Data sheet with same GP No.

 

[Debraj]

Hi abid.aries


Try below formula..

=IFERROR(INDEX(Data!$K$2:$K$17,SMALL(IF((Data!$B$2:$B$17=Report!$K$5)*(Data!$O$2:$O$17=Report!$M$5),ROW(Data!$B$2:$B$17)-1),ROW(A1))),"")

https://dl.dropboxusercontent.com/u/78831150/Excel/Gate%20Pass%20Details%20%28abid.aries%29.xls


for more detail please visit below URL

http://chandoo.org/wp/2011/11/18/formula-forensics-003/


Regards,

Deb

 

[abid.aries]

Hi Debraj Ray

Great your formula is working


Thanks alot

 

[Debraj]

Hi AbidAries!


Thanks for the complement..


Regards,

Deb