Microsoft 365 and Office | Excel | For business | Other
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data
=INDEX(B:B,MATCH(C2,A:A,0))
How to compare cells in one column to a second column and display values from a third column.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous
Hello all,
I think I should start with tables, as it's the clearest way of asking my question. Here is what I am starting with;
| A | B | C | D |
|---|---|---|---|
| Country Number | Country | Search Number | Search Result |
| 1 | Austria | 2 | |
| 2 | Belgium | 3 | |
| 3 | Czechia | 5 | |
| 4 | Denmark | 7 | |
| 5 | Estonia | 11 | |
| 6 | Finland | 13 | |
| 7 | Germany | 17 | |
| 8 | Hungary | 19 | |
| 9 | Ireland | 23 | |
| 10 | Japan | ||
| 11 | Kazakhstan | ||
| 12 | Latvia | ||
| 13 | Monaco | ||
| 14 | Norway | ||
| 15 | Oman | ||
| 16 | Poland | ||
| 17 | Qatar | ||
| 18 | Romania | ||
| 19 | Slovakia | ||
| 20 | Turkey | ||
| 21 | Ukraine | ||
| 22 | Venezuela | ||
| 23 | Wales | ||
| 24 | Xanadu | ||
| 25 | Yemen | ||
| 26 | Zambia |
Here is what I want;
| A | B | C | D |
|---|---|---|---|
| Country Number | Country | Search Number | Search Result |
| 1 | Austria | 2 | Belgium |
| 2 | Belgium | 3 | Czechia |
| 3 | Czechia | 5 | Estonia |
| 4 | Denmark | 7 | Germany |
| 5 | Estonia | 11 | Kazakhstan |
| 6 | Finland | 13 | Monaco |
| 7 | Germany | 17 | Qatar |
| 8 | Hungary | 19 | Slovakia |
| 9 | Ireland | 23 | Wales |
| 10 | Japan | ||
| 11 | Kazakhstan | ||
| 12 | Latvia | ||
| 13 | Monaco | ||
| 14 | Norway | ||
| 15 | Oman | ||
| 16 | Poland | ||
| 17 | Qatar | ||
| 18 | Romania | ||
| 19 | Slovakia | ||
| 20 | Turkey | ||
| 21 | Ukraine | ||
| 22 | Venezuela | ||
| 23 | Wales | ||
| 24 | Xanadu | ||
| 25 | Yemen | ||
| 26 | Zambia |
What I want my formula to do, in English is; Check if the values in column C (Search Number) exist in Column A (Country Number) and if so, display the corresponding country from column B (Country) in column D (Search Results).
The actual dataset I am working on is 30,000 plus rows long with 12 digit serial numbers and 10 character alpha-numeric codes. Doing this manually is not an option.
Thank you in advance.
Microsoft 365 and Office | Excel | For business | Other
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Anonymous
2 additional answers
Sort by: Most helpful
-
Anonymous
2023-07-21T10:03:17+00:00 You are welcome.
-
Anonymous
2023-07-21T09:32:46+00:00 Thank you very much! Exactly what I wanted.