Excel - Find and return a specific number?


I have 3 columns, each showing a result from a test as follows:

3 =fail  
2 =marginal pass  
1 = pass 

The person will only re-take the test a second time if they fail the first time. And if they fail twice, they have to take it a third time.

So my 3 columns could just show 1 or 2 in the first column and nothing in the second. Or they could show 3 in the first, and 1 or 2 in the second etc.

I want to run a macro or create a formula that looks across the 3 columns, and displays the latest result, i.e. the answer in the last column.


name test1 test2 test3  
a 3 3 1  
s 2  
d 3 1  
f 3 2
  • Now in E2 type this formula (or copy paste)
  • Copy E2 down
    • You will get:
name test1 test2 test3 result  
a 3 3 1 1  
s 2  2  
d 3 1  1  
f 3 2  2 

Note that

Thanks to venkat1926 for this tip on the forum.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Find and return a specific number? », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).