Update inputs from different sheets based on data in different cells

[k_hoover]

I hope I can explain this in a way that makes sense. I have an established workbook with what is essentially a "master" and "sub" worksheets, where the master contains every bit of data contained within the entire workbook. What I am hoping to accomplish is find a formula that will update select cells in the row by only updating the master. There is too much data to just use ='Master' for the entire workbook.

Here is my example. Here is the master:

"Master"

Here is sheet 2:

"Sheet 2"

I am trying to use a formula in C5 of sheet 2 to search for the value of B2 (So "R") in Column B on the 'Master', then populate the data from the master of the corresponding row C. Meaning I would like Sheet 2, C5 to equal C18 of the Master.

I thought I was going to able to achieve this using an IF formula, but as getting a #SPILL error. 

Any help offered would be appreciated. Thanks.

[#2 · Accepted solution]

@k_hoover 

=INDEX(Sheet1!C:C,MATCH(1,(Sheet2!A1=Sheet1!A:A)*(Sheet2!B1=Sheet1!B:B),0))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

Sheet1:

Sheet2:

 

[#3]

That worked. Thank you. I would have never gotten to INDEX.