CODEWITHSUNDEEP
excelexcel-formula
Shino88
Shino88

Reputation: 125

Need help creating formula to check cell against value then copy correct value from separate worksheet

I'm trying to create a formula to check if the value in a cell has the string correct or incorrect. If the value in the cell is incorrect then a value from another worksheet should be copied into current worksheet. Below is an example worksheet.

Worksheet 1

Col A   Col B    Col C   Col D
-------------------------------
A       1        Correct
B       2        Correct
C       1        incorrect

Worksheet 2

Col A   Col B    Col C
----------------------
 A       1      Correct
 B       2      Correct
 C       3      Correct

Using the example worksheet above the first and second row of column D should be empty as there corresponding cells in column C have the value correct, but in the third row of column D the value 3 from column B worksheet 2 should be copied into the cell as the third row of column C has has value incorrect.

Upvotes: 1

Views: 804

Answers (1)

Nick Perkins
Nick Perkins

Reputation: 1327

I'm going to assume that the data in Worksheet 1, Column 2 is being tested because it has been entered manually. Otherwise, a formula could provide the correct answer every time.

Because there isn't much detail, I've looked at it as if it was some type of test sheet, with the test on Worksheet 1 and the answers on Worksheet 2.

I used a VLOOKUP in both Column C and D. In column C, the formula I used is:

=IF(VLOOKUP(Sheet1!$A2,Sheet2!$A$1:$B$3,2,FALSE)=$B2,"Correct","Incorrect")

This gives you the Correct or Incorrect value in Column C. We can then use the same VLOOKUP a second time to return the correct answer in Column D, if required.

=IF($C2="Incorrect",VLOOKUP(Sheet1!$A2,Sheet2!$A$1:$B$3,2,FALSE),"")

It would be more efficient to do the vlookup once, hold that value in a different column, and then point to that column in column C or D. You could then hide that column and lock down the sheet to hide that value if that was a concern. However I used your example output without the additional column.

I've attached a screenshot of the working example. Hope this is the right track - if not leave a comment and we can work it out.

Screenshot of working example

Upvotes: 2

Related Questions