Search code examples
excelexcel-formulaworksheet-functionexcel-2016

Generate ID based on text

I have an Excel spreadsheet with a simple table on it. The purpose of it is to log the price of a cryptocurrency I'm watching.

TABLE

When I'm entering new data, I make a new line, put in the time and date, write which currency it is and whether it's down or up from my last listing.

I'm wanting to put an ID column in to organise each cryptocurrency in how they're listed in the top 100 currencies.

In this list you have ARK, EOS, FunFair, IOTA, and NEM. The order is IOTA, NEM, EOS, ARK, FunFair in the list. So if I sort by Item/CC it should go by time/date, then ID, then name putting IOTA at the top with a simple 1 in the column.

The question is, how do I write something like: 

=if C:C = "IOTA" B:B = "1"
Solution

  • In B2 and copied down should suit:

    =MATCH(LEFT(C2),{"I","N","E","A","F"},0)

    Alternatively set up a Custom List (how is off topic here) and sort ColumnC on that.