Excel - Find and return a specific number?

October 2017




Issue


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.

Solution


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)


=IF(D2<>0,D2,IF(C2<>0,C2,B2)) 
  • 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.
Published by aakai1056. Latest update on September 5, 2011 at 01:19 PM by aakai1056.
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).