How do I sort two columns in excel by values matching column A?

excel vlookup columnsorting
user3419585 picture user3419585 · Jun 6, 2014 · Viewed 37.5k times · Source

Hi I have two columns in excel with numbers. Column A has more numbers than Column B. I want to sort Column B by values that match in Column A If the value in column A cannot be found in column B i want column B to display "MISSING" I have already sorted both columns in ascending order.

eg. of the output I want: 

A       B  
2001    2001  
2002    2002  
2003    MISSING  
2004    2004  
2005    MISSING  
2006    MISSING  
2007    2007

Please suggest what formula I should use. Thanks

Answer

witchchild picture witchchild · Jun 6, 2014

Count the number of occurrences of the value in col A in col B. If the number is 0 then it is missing.

=IF(COUNTIF(B:B,A1)=0,"Missing",A1)

Related questions

Remove #N/A in vlookup result

How do I modify this function so that the result will merely be a blank cell rather than having #N/A show up if B2 has nothing in that cell? I think I might need something like an ISERROR check …

Read More
Excel - find cell with same value in another worksheet and enter the value to the left of it

I have a report that is generated in Excel which contains an employee's number, but not his/her name. Not every employee will be on this worksheet on any given day. In a 2nd worksheet I have a list of …

Read More
Comparing two columns, and returning a specific adjacent cell in Excel

I am using a combination of if, vlookup, match, iserror functions, and unfortunately I've not been able to find the right formula. Comparing two columns for matches is easy enough. the tough part has been returning a specific cell once …

Read More