Referencing a value in a column to lookup another value in a row

[Reboshua]

https://docs.google.com/spreadsheets/d/1RX7wmDwwPSknjihiw2_br6z2mwjgMP3_7jYNDt8LMsI/edit?usp=sharing

Apologies if posting to Google Docs is bad form here, but I couldn't think of a better way to describe my problem. Sheet 1 has the names and performance ratings of 5 people. Ratings are 1-5. Sheet 2 has these same people, but with the performance ratings as values in the first row, and the percentage of time they achieved these ratings in the values below.

My question: what excel formula, or combination of formulas, do I need for column C of the first sheet to be able to lookup the percentages that match both the name of the person and the performance ratings in columns A and B of sheet 1 in the table on sheet 2?

I hope that wasn't too confusing.

Thank you in advance!

 

[Aladin Akyurek]

Here are some methods for posting directly on the forum:
http://www.mrexcel.com/forum/about-board/508133-attachments.html
https://app.box.com/s/soezox25h3w0q5s4rcyl

Using such allows for a rather more permanent record on the board.

 

[Reboshua]

Thank you, Aladin. I'll use that next time!

 

[Aladin Akyurek]

Thank you, Aladin. I'll use that next time!

Why not use now? Please add also what you expect to see (the concrete values) in your post.

 

[Reboshua]

Okay, here goes. I wasn't sure if it would handle multiple sheets. Here is what's on Sheet 1

Row\Col	A​	B​	C​
1​	Name	Score	% of time
2​	Joe	1​
3​	Sally	2​
4​	Bob	3​
5​	Tom	4​
6​	Jim	5​

Here is sheet 2

Row\Col	A​	B​	C​	D​	E​	F​
1​	Name	1​	2​	3​	4​	5​
2​	Joe	23.00%​	25.00%​	15.00%​	16.00%​	21.00%​
3​	Sally	26.00%​	42.00%​	15.00%​	9.00%​	8.00%​
4​	Bob	55.00%​	10.00%​	12.00%​	15.00%​	8.00%​
5​	Tom	15.00%​	34.00%​	25.00%​	10.00%​	16.00%​
6​	Jim	18.00%​	25.00%​	36.00%​	8.00%​	13.00%​

I am trying to populate column c in sheet 1 with values from sheet 2. I would expect

C2 to have the value 23%
C3 to have the value 42%
C4 to have the value 12%
C5 to have the value 10%
C6 to have the value 13%

 

[Aladin Akyurek]

Congrats. You created a perfect post...

C2, copied down:

=INDEX(Sheet2!$B$2:$F$6,MATCH($A2,Sheet2!$A$2:$A$6,0),MATCH($B1,Sheet2!$B$1:$F$1,0))

 

[Reboshua]

Thank you for guiding me. When I pasted this formula into C2, it returned a value of #N/A. Thoughts?

 

[Aladin Akyurek]

Thank you for guiding me. When I pasted this formula into C2, it returned a value of #N/A. Thoughts?

Oops..

=INDEX(Sheet2!$B$2:$F$6,MATCH($A2,Sheet2!$A$2:$A$6,0),MATCH($B2,Sheet2!$B$1:$F$1,0))

That should have been $B2, not $B1.

 

[Reboshua]

Thanks! That worked perfectly.

 

[Aladin Akyurek]

Thanks! That worked perfectly.

You are welcome.