How do I Extract Multiple Rows using Index and Match

[Nikki225]

I have a spreadsheet where I'm tracking Employee Salary History information on one sheet and have a summary page on another sheet.

There are 3 columns on the Employee Salary History Information Sheet: Date, Description and Amount

On the Summary Page, I have a drop down box in cell B1 that allows me to select the employee name and I use VLookups to pull in the Hire Date, DOB and other relevant employee information.

I'd like to add an area to the bottom of this that will pull in the Date, Description and Amount from the Employee Salary History Information Sheet for the specific employee.

I don't have experience with array formulas and it seems like this would be the best solution for what I'm looking for.

Could you please help me with the formula?

 

[Aladin Akyurek]

On sheet 3, it is A2:D25. The range excludes the headers but the 4 columns contain: Date, Employee, Description and Amount

Sheet1

In A8 enter:

=COUNTIFS(Sheet3!$B$2:$B$25,$B$1)

A9: idx, B9: date, C9: employee, D9: description, E9: amount

A10, control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$10:A10)>$A$8,"",SMALL(IF($B$1=Sheet3!$B$2:$B$25,
    ROW(Sheet3!$B$2:$B$25)-ROW(Sheet3!$B$2)+1,ROWS($A$10:A10)))

B10, just enter, copy across to E10, and down:

=IF($A10="","",INDEX(A$2:A$25,$A10))

This should be fast and robust. And I hope I got the layout right.

 

[Bacco]

Struggling with something similar here...
I have a table with a daily account log, where column G has drop down menu with the choices "Direct" and "Bank Deposit"
B12: ReceiptNo. C12:Date D12:Name E12:Receipt Type F12:Code G12:Payment Type H12:Ref No. I12:Value

I am trying to populate a MONTHLY table with information JUST on bank deposits from the daily account logs, which is structured under this heading rows:
K12:Date L12: Name M12:Ref No. N12:Value

I am trying to use an array formula to identify registers where G is "Bank deposit", and then populate the table with relevant registers.

I've tried to adapt the formula from here, but it gives me blanks. (Where this formula is used in L12:Name column, and K11 has lookup term "Bank deposit"
=IF(ISERROR(INDEX($B$12:$I$17;SMALL(IF($G$12:$G$17=$K$11;ROW($G$12:$G$17));ROW(1:1));3));"";INDEX($B$12:$I$17;SMALL(IF($G$12:$G$17=$K$11;ROW($G$12:$G$17));ROW(1:1));3))

As mentioned, this formula is just generating blanks. And, even if it was working, it wouldnt help me populate the rest of the columns K,M&N...

I've tried to change the ROW(1:1) to ROW(13:13), without effect.

Any ideas on a better plan? Help is much appreciated!

Excel 2007 on Windows

 

[Aladin Akyurek]

Struggling with something similar here...

Where do you have your headers? (Your description is such that it is hard to know where the data begins distinct from headers if the latter exist.)

 

[Bacco]

Where do you have your headers? (Your description is such that it is hard to know where the data begins distinct from headers if the latter exist.)

Sorry! Was trying to be clear about this.
This is the daily register table DAILY

	B	C	D	E	F	G	H	I
12	Receipt No.	Date	Name	Description	Code	Payment type	Ref No.	Value
13
14
15
16
17

<tbody>
</tbody>

This is the monthly bank deposit register, on sheet BANK

	B	C	D	E
12	Bank deposit
13	Date	Name	Ref No.	Value
14		X
15
16

<tbody>
</tbody>

(note: i've now changed the reference cells on the table above, to reflect that it is actually on a different page in my worksheet. thus the formula i previously mentioned would now look like this (for cells in the NAME column of the BANK sheet):

=IF(ISERROR(INDEX('DAILY!'$B$12:$I$17;SMALL(IF('DAILY'!$G$12:$G$17=$B$12;ROW('DAILY'!$G$12:$G$17));ROW(1:1));3));"";INDEX('DAILY'!$B$12:$I$17;SMALL(IF('DAILY'!$G$12:$G$17=$B$12;ROW('DAILY'!$G$12:$G$17));ROW(1:1));3))

Thanks in advance!

 

[Aladin Akyurek]

Sorry! Was trying to be clear about this.
This is the daily register table DAILY

	B	C	D	E	F	G	H	I
12	Receipt No.	Date	Name	Description	Code	Payment type	Ref No.	Value
13
14
15
16
17

<tbody>
</tbody>

This is the monthly bank deposit register, on sheet BANK

	B	C	D	E
12	Bank deposit
13	Date	Name	Ref No.	Value
14		X
15
16

<tbody>
</tbody>

(note: i've now changed the reference cells on the table above, to reflect that it is actually on a different page in my worksheet. thus the formula i previously mentioned would now look like this (for cells in the NAME column of the BANK sheet):

=IF(ISERROR(INDEX('DAILY!'$B$12:$I$17;SMALL(IF('DAILY'!$G$12:$G$17=$B$12;ROW('DAILY'!$G$12:$G$17));ROW(1:1));3));"";INDEX('DAILY'!$B$12:$I$17;SMALL(IF('DAILY'!$G$12:$G$17=$B$12;ROW('DAILY'!$G$12:$G$17));ROW(1:1));3))

Thanks in advance!

In F13 of BANK enter: Idx

In B14 of BANK enter, copy across to E14, and down:

=IF($F14="","",INDEX($B$13:$I$17,$F14,MATCH(B$13,DAILY!$B$12:$I$12,0)))

In F14 of BANK control+shift+enter, not just enter, and copy down:

=IFERROR(SMALL(IF($G$13:$G$17=$B$12,ROW($G$13:$G$17)-ROW($G$13)+1),
    ROWS($F$14:F14))),"")

 

[Nikki225]

I had to play around with this to understand the concept. This is a really great formula and I've been able to apply it to so many of the spreadsheets that I work on regularly. Thanks for your help!

 

[Aladin Akyurek]

I had to play around with this to understand the concept. This is a really great formula and I've been able to apply it to so many of the spreadsheets that I work on regularly. Thanks for your help!

You are welcome. Thanks for the update.