Comparing values from different sheets in excel

Bluew

Hi,

I've been searching for an answer to my question almost the entire day but can't seem to get it right, so hopefully Smartsheet can be my savior here 🙏

So I have Sheet1 in Excel with two columns, both with texts in it. Each row has a different word in it: column A, row 1=text1, column B, row 1=bananas, column A, row 2= text2, column B, row 2= apples, column A, row 3= text3, column B, row 3=oranges etc. So it kinda looks like this:

Column A Column B

text1 bananas

text2 apples

text3 oranges

Now, I have the same columns in Sheet2, but this time the text from column B is missing so I onli have the column A with text1, text2, text3 etc..and here's the thing. I want to manually insert the values from each row in column B in sheet 2, and if the values that i just entered matches the value from sheet1 depending on the criteria from column A, to have a 3rd column in sheet2, to say if it's correct or wrong. And the rows in sheet2 don't necessarily have to be in the same order as in sheet1. Something like this:

Sheet2:

Column A Column B Column C

text2 *if insert apples true (false if i insert bananas as bananas is corresponding to text1)

text3 oranges true

text1 oranges false

Scope of workbook: I'm learning a new language and I just thought of creating something like an excel vocabulary. Basically, the Sheet1 is a list of words I've learnt or planning to learn, with column A the words in the foreign language and column B their translation in my native language. And in Sheet2 i want to check if I learned the words correctly by having in column A the words in my native language and typing in their corresponding in the foreign language and check in column 3 if that's correct.

Hope this is something you guys can help me with 🙏🙏

Thank you so much!

jmyzk_cloudsmart_jp

Hi @Bluew

You can use the cross-sheet reference formula to reference values in another sheet, get the correct answer, and compare it with the answer in this case.

https://youtu.be/wdqGmdke4GA

Example Formula:

=IF(JOIN(COLLECT({English}, {Italian}, Italian@row)) = English@row, "true", "false")

In the formula, {English} is a column range in the Answer sheet in the second image below. And so is the {Italian}.

So, COLLECT({English}, {Italian}, Italian@row) get the {English} range whose {Italian} range matches Italian@row value. So, if the Italian@row is mela, the formula gets the " apple " range. The JOIN function joins the values of a range and returns, concatenating the value in the range. (Since the range only has "apple", it returns "apple")

Then, you can compare the value with the answer in the English column.

A more typical formula uses INDEX(MATCH());

=IF(INDEX({English}, MATCH(Italian@row, {Italian}, 0)) = English@row, "true", "false")

https://app.smartsheet.com/b/publish?EQBCT=29634a26f3e0499c86397730376cb64a