Forum Discussion
Update inputs from different sheets based on data in different cells
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.
=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:
2 Replies
=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:
- That worked. Thank you. I would have never gotten to INDEX.
