multifilter data on new sheet by 2 values

[JAlandhar 201718]

Respected all the members,

I have a new query as I have to filter the data from the database using 2 filter on a new sheet, the old result was so useful to me, I need to modify that result further. Please let me know is that possible to modify the TEST sheet I'm uploading with the new features.


the thing I wanted was to auto update the voucher_no in the data using the specific code in the database, now what I want is to update the same thing but with Subledger(column) value. like I need the data only whose SUBLEDGER value is 1 and of the provided number

PIC 1 is of all the database,

PIC 2 is of filtered subledger_value = "1

",
PIC 3 is the value want to look for in the filtered data,
PIC 4 is what I want in result.

a demo file"TEST.xlsx" is attached to, please someone help me.

 

[GraH - Guido]

Possible like tis:
In C1 use a countifs, and in the table an aggregate within an index.
=IF(ROWS($A$6:A6)>Number_of_Entries,"",INDEX(FDDR[LEDCODE],AGGREGATE(15,6,ROW(FDDR[VOUNO])/((FDDR[LEDCODE]=Entered_LEDCODE)*(FDDR[subledger]=$H$1))-1,ROWS($A$6:A6))))

the red part needs to refer to the required result:
LEDCODE VOUNO SUBLEDGER
see attached file.

 

[JAlandhar 201718]

Possible like tis:
In C1 use a countifs, and in the table an aggregate within an index.
=IF(ROWS($A$6:A6)>Number_of_Entries,"",INDEX(FDDR[LEDCODE],AGGREGATE(15,6,ROW(FDDR[VOUNO])/((FDDR[LEDCODE]=Entered_LEDCODE)*(FDDR[subledger]=$H$1))-1,ROWS($A$6:A6))))

the red part needs to refer to the required result:
LEDCODE VOUNO SUBLEDGER
see attached file.

the thing you said is good, but not working

please check the screenshot attached

 

[GraH - Guido]

Which version of Excel do you have? Does it knows the function aggregate? That one exists since version 2010. I suspect you have 2007. Then other formulae are needed.

It might be this one (CSE):
B6: =IF(A6="","",INDEX(FDDR[VOUNO],SMALL(IF(FDDR[subledger]=$H$1,IF(FDDR[LEDCODE]=Entered_LEDCODE,(ROW(FDDR[LEDCODE])-ROW(FDDR[[#Headers],[LEDCODE]])))),ROWS($A$6:A6))))