Comparing values from different sheets in excel
![Bluew](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
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!
Answers
-
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.
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!