SOLVED

# Update inputs from different sheets based on data in different cells

Copper Contributor

# 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.

2 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Update inputs from different sheets based on data in different cells

``=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:

# Re: Update inputs from different sheets based on data in different cells

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

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Update inputs from different sheets based on data in different cells

``=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: