You are here:

Excel/Excel finding all first occurances


Dear Tom,

I have a query that I have been searching the internet to find the answer to but to no avail. You have helped me once before so I hope you are able to help me again.

I want to create a formula which will look at column A to find the first occurance of each ID number and then examine column B.

I am using Excel 2003.

Below is an example table.

ID   Color
123   Red
132   Blue
444   Red
132   Red
122   Yellow
444   Blue
444   Green
111   Red
122   Blue

I would like a formula for the following:

If the first occurance of ID (in column A) equals red (column B) then "red", If the first occurance of ID equals yellow then "yellow", If first occurance of ID does not equal red or yellow then "other". Subsequent occurances of ID can be ignored.

I hope this makes sense. Please be gentle with me as I am not a very confident Excel user.

Thank you


It is unclear to me where you want the results or how you want to search.  I will assume that
A1:  ID
B1:  Color
A2:  Red
B2:  Blue

so in C2 put in


now drag fill this formula down column C next to your data.

In my test worksheet with test data I generated, I got a result like this:

ID   Color   
2222   Green   Other
3333   Yellow   Yellow
3333   Purple   
3333   Red   
1111   Red   Red
2222   Yellow   
1111   Red   
3333   White   
2222   Green   
2222   Blue   
1111   Yellow   

Hopefully that is what you want.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.