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

Reboshua

Board Regular

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.

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Thank you, Aladin. I'll use that next time!

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.

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%

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))

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

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.

Thanks! That worked perfectly.

Replies
0
Views
308
Replies
2
Views
133
Replies
1
Views
137
Replies
2
Views
172
Replies
6
Views
274

1,203,526
Messages
6,055,924
Members
444,835
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back