Excel - Find and return a specific number?

October 2016


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.

Related :

This document entitled « Excel - Find and return a specific number? » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.