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
1	Grant 1	Grant 2
2	500	20
3	732
4

<tbody>
</tbody>

Sheet2​

Array Formulas Cell Formula A2 {=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))),""))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>