Multi return of data

[sathis85]

Hi all,


kindly go through the below link and pls send me the good solution

https://dl.dropbox.com/u/97330471/Multi%20return%20of%20data.xlsm

 

[kaushik03]

Hi sathis85,


Can you please check your file here and let us know if this is fine...


https://hotfile.com/dl/166914083/979c5fd/Multi_return_of_data.xlsx.html


Note:

1)I have written the formula at H4 and copy it all the way down.

2) You can examine the formula by pressing F2 (and F9) but but remember to enter it as ctrl+shift+enter( instead of enter only) as it is an array formua.


Regards,


Kaushik

 

[Hui]

Select K2:K10

Enter

=IFERROR(INDEX(Data!$C$2:$C$40,ROW(Data!$C$2:$C$40)*(Data!$A$2:$A$40=$I$2)*(Data!$B$2:$B$40=$I$3)-1),"") [b] Ctrl+Shift+Enter


or


=IFERROR(INDEX(Table1[Account],ROW(Table1[Account])*(Table1[Salesperson]=$H$2)*(Table1[Region]=$H$3)-1),"")

Ctrl+Shift+Enter[/b]

 

[sathis85]

Hi Hui sir,


=IFERROR(INDEX(Table1[Account],ROW(Table1[Account])*(Table1[Salesperson]=$H$2)*(Table1[Region]=$H$3)-1),"")


In this formula you have used name manager. pls guide me how to use name manager for the hole table as like you applied in the work book" Table1".


Reg,

Sathis

 

[Hui]

Sathis


I'm unsure about your question as the solution doesn't use the name manager at all


To enter the solution

Copy the formula above

Select the cells K2:K10

Press F2 and Ctrl V to paste the formula in

Now press Ctrl+Shift+Enter

Thats it


Don't press Enter by itself as this is an Array Formula not a normal formula

 

[NARAYANK991]

Hi Sathis ,


You can learn more about how to use formulae with tables here :


1. http://office.microsoft.com/en-in/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx


2. http://blogs.office.com/b/microsoft-excel/archive/2005/10/28/tables-part-3-using-formulas-with-tables.aspx


3. http://www.databison.com/index.php/table-formulas-in-excel/


Narayan