Update inputs from different sheets based on data in different cells

Copper Contributor

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:


Here is sheet 2:

"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 Replies
best response confirmed by Hans Vogelaar (MVP)



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







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