need help

[uswyne]

[TABLE="width: 1043"]
<colgroup><col><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]G/L - Expense[/TD]
[TD]Account Title[/TD]
[TD]GR No[/TD]
[TD]Invoice Date[/TD]
[TD]Name[/TD]
[TD]Period[/TD]
[TD]Class[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Admission Fee[/TD]
[TD]12[/TD]
[TD]2-Aug-17[/TD]
[TD]Alizay Ali[/TD]
[TD] ------[/TD]
[TD]2[/TD]
[TD] 25,000.00[/TD]
[/TR]
[TR]
[TD]1007[/TD]
[TD]Computer Fee[/TD]
[TD]12[/TD]
[TD]2-Aug-17[/TD]
[TD]Alizay Ali[/TD]
[TD]Aug/ Sep 2017[/TD]
[TD]2[/TD]
[TD] 2,000.00[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Tution Fee[/TD]
[TD]12[/TD]
[TD]2-Aug-17[/TD]
[TD]Alizay Ali[/TD]
[TD]Aug/ Sep 2017[/TD]
[TD]2[/TD]
[TD] 17,600.00[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]Security Deposit[/TD]
[TD]12[/TD]
[TD]2-Aug-17[/TD]
[TD]Alizay Ali[/TD]
[TD] -----[/TD]
[TD]2[/TD]
[TD]25,000.00[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Tution Fee[/TD]
[TD]13[/TD]
[TD]2-Aug-17[/TD]
[TD]Urwa Amin Khan[/TD]
[TD]Aug/ Sep 2017[/TD]
[TD]PG[/TD]
[TD] 10,000.00[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]Security Deposit[/TD]
[TD]13[/TD]
[TD]2-Aug-17[/TD]
[TD]Urwa Amin Khan[/TD]
[TD] [/TD]
[TD]PG[/TD]
[TD] 25,000.00[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Tution Fee[/TD]
[TD]14[/TD]
[TD]3-Aug-17[/TD]
[TD]Aliza fatima [/TD]
[TD]Aug/ Sep 2017[/TD]
[TD]2[/TD]
[TD] 22,000.00[/TD]
[/TR]
[TR]
[TD]1007[/TD]
[TD]Computer Fee[/TD]
[TD]14[/TD]
[TD]3-Aug-17[/TD]
[TD]Aliza fatima [/TD]
[TD]Aug/ Sep 2017[/TD]
[TD]2[/TD]
[TD] 2,000.00[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]Security Deposit[/TD]
[TD]15[/TD]
[TD]4-Aug-17[/TD]
[TD]Zaid Malik[/TD]
[TD] [/TD]
[TD]Nur[/TD]
[TD] 25,000.00[/TD]
[/TR]
[TR]
[TD]2000[/TD]
[TD]Security Deposit[/TD]
[TD]16[/TD]
[TD]4-Aug-17[/TD]
[TD]Dua Malik[/TD]
[TD] [/TD]
[TD]2[/TD]
[TD] 25,000.00[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Tution Fee[/TD]
[TD]15[/TD]
[TD]4-Aug-17[/TD]
[TD]Zaid Malik[/TD]
[TD]Aug/ Sep 2017[/TD]
[TD]15000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Tution Fee[/TD]
[TD]16[/TD]
[TD]4-Aug-17[/TD]
[TD]Dua Malik[/TD]
[TD]Aug/ Sep 2017[/TD]
[TD]16500[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1007[/TD]
[TD]Computer Fee[/TD]
[TD]16[/TD]
[TD]4-Aug-17[/TD]
[TD]Dua Malik[/TD]
[TD]Aug/ Sep 2017[/TD]
[TD]2000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]


REPORT[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="colspan: 2"]Aug/Sep 2017[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GR No[/TD]
[TD]Class[/TD]
[TD]Name[/TD]
[TD]Security Deposit[/TD]
[TD]Admission Fee[/TD]
[TD]Tution Fee[/TD]
[TD]Computer Fee[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]2[/TD]
[TD]Alizay Ali[/TD]
[TD]25000[/TD]
[TD]11/06/1968[/TD]
[TD]17600[/TD]
[TD]2000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


I want that, the data fetch by putting GR No. in report

 

[Misca]

You can find the text values with INDEX and MATCH. For the number values you can use SUMIFS (or SUMPRODUCT if you're using Excel 2003 or older).

If your data table is on the same sheet as your Report the INDEX and MATCH solution would look something like:

=INDEX(Class;MATCH($J2;GR_No;0)) for class number and
=INDEX(Name;MATCH($J2;GR_No;0)) for Name.

I'm using named ranges in my formulas to make them easier to understand but normal ranges would work just as well. J2 is where the GR No I'm looking for is found.

The INDEX MATCH -formulas get their values from the first row where they find the GR No Match. This alone wouldn't work for the numeric values because you're going to need to look up not only the GR No (or the name) but also the Account Title. That's where the SUMIFS comes handy:

=SUMIFS(Amount;Name;$L2;AccountTitle;M$1)

In my formula the Amount, Name and AccountTitle are named ranges again. L2 is where the name I'm looking for is found and M1 is where the formula gets the Account Title it's looking for ("Security Deposit").

I've used mixed cell references in my lookup formulas so that I can use the same formula in all the columns / rows.