Question: INDEX and MATCH

[clandestino800]

Hi guys!

I am struggeling with a formula, which i i think it can be solved with index and match.
In Sheet1 there are three columns (Names, Proj and Amount) and in Sheet2 I have Proj and the names (vertically)
and I'm trying to get the amounts from Sheet1 assigned to the name.

I'd be very thankful for any help!
Randall

Sheet1

Sheet2

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IFNA(INDEX(Sheet1!$C$2:$C$100,MATCH($A2&"|"&B$1,Sheet1!$B$2:$B$100&"|"&Sheet1!$A$2:$A$100,0)),"")

this may need to be confirmed with Ctrl Shift Enter depending on which version of Xl you are using it in.

 

[Marcelo Branco]

You can also try SUMIFS

Assuming search values in Sheet2 are : Projetcs at A2:A5 ; Names at B1:M1

Try
B2 copied across and down
=SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,B$1,Sheet1!$B:$B,$A2)

M.

 

[clandestino800]

Thanks a lot ! I used a mix of your recommendations and achieved what I needed!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[dcardno]

Have you considered a pivot table?