Return a list of values if another cell contains X

[remobec]

OK, I am working on an accounting sheet to keep track of grant-related expenses.

On Sheet 1, I have:
Expense | Grant

Expense is the amount. Grant is the grant it is associated with and chosen from a drop down menu.

So it might look like:
A | B
$500 | Grant 1
$20 | Grant 2
$732 | Grant 1

On Sheet 2, I am trying to list all the grant expenses for each grant.

I want it to say something like: (for Cell A2) If Sheet1, Column B equals A1, then put in the amount from Sheet 1, Column A

So it might look like:
A | B
Grant 1 | Grant 2
$500 | $20
$732 |

I have tried IF formulas and VMATCH and not really sure how to get Sheet 2 to work right.

Thank you!

 

[Eric W]

Given your layout, try this:

	A	B	C
Grant 1	Grant 2

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]732[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]{=IF(A1="","",IFERROR(INDEX(Sheet1!$A:$A,SMALL(IF(Sheet1!$B$1:$B$100=A$1,ROW(Sheet1!$A$1:$A$100)),ROWS(A$2:A2))),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]