Problem with Index and Match formula

Hi Friends,

A Very Good Evening...

I am doing index and match formula for my report.

Situation is I have one "raw data" sheet and another "LE 3598" sheets which is related to "raw data" sheet. both are in one excel file.

I am applying Index and match formula in "LE 3598" sheet to get the some results from "raw data" sheet. My problem is if in "LE 3598" sheet if want GL data related to John cater from "raw data" sheet. then it gives the only first line GL account. But I have more that one GL account for the John Cater. Is there any way or formula to get all GL for John cater.

Some more brief:

1.Amount Credited to John cater total 25000.00 ( which shown in LE 3598)

2. I have the breakup data of 25000.00 to different G/L accounts in raw data sheet.

so I need the GLs accounts related to Amount 25000.00



Could you please help on this.

Thanks & Regards,
Pradeep

You need to have a unique reference to each of your records in the raw data sheet, so you can have a helper column (assume column H) with a formula like this:

=A1&"_"&COUNTIF(A$1:A1,A1)

assuming your names are in column A - this will add a unique sequential number to the end of each name.

Then in your summary sheet you can have a formula like this:

=INDEX('raw data'!B:B,MATCH($N$1&"_"&ROWS($1:1),'raw_data'!$H:$H,0))

assuming the name is in a fixed cell (N1), and that you want to get the data from column B. Copy across for other columns, then copy down to get the other records (as ROWS($1:1) will become ROWS($1:2), then ROWS($1:3) on subsequent rows).

Hope this helps.

Pete

Hi Pete,

Thanks for your help.....

Here I am getting confusion.. I have attached the file for more details;

In file I want result against the documents number (column B) from the raw data sheet.

In column B there one document number is there but in Raw data sheet there are so many GLs have been mentioned against that one Document number and you can find amount -40865.02 and it has been booked with some many GL with different amount. If you sum the one document's total in raw data sheet it will match with single line document number amount in LE 1153 sheet.So I need that GL A/cs with break up amount against the document number


In both file Document number is unique reference.


Could you please help on this...

Thanks & Regards,
Pradeep

Where is the text in column F of the LE 1153 sheet meant to come from?

Does the Raw data sheet contain entries for other company codes, but you just want to list those entries with a code of 1153?

Did you try my earlier suggestions, with the cell references adjusted to suit your data?

Pete