Hoshyar,

If I've understood your requirement correctly, it is as follows:

(a) You have a column of data with 0 < rowcount < 2000 or thereabouts

(b) A valid entry in the column must be one of seven category names, as
follows:
1. Office Equip
2. Ops Equip
3. SHE Equip
4. Security Equip
5. It Equip
6. Comms Equip
7. Vehicels Equip

(c) For each row in your column of data, you want to return a
corresponding reference
number, as per the following:
Category Ref
1. Office Equip 100
2. Ops Equip 200
3. SHE Equip 300
4. Security Equip 400
5. It Equip 500
6. Comms Equip 600
7. Vehicels Equip 700

I would do it like this:

1. Copy your column of data to the range A1:An where n is the count of
rows
2. In the range D1:D7, enter the category names
3. In the range E1:E7, enter the corresponding reference numbers
4. Sort the range D1:E7 on "Column A"
5. In cell B1, enter the formula: =VLOOKUP(A1,$D$1:$E$7,2)
6. Copy the contents of cell B1down to cell Bn where n is the count of
rows
7. You should now see the correct reference numbers in column B.

If you want to get more sophisticated, you can also validate column A
values by comparing its contents with a VLookup on column 1 of the
lookup range, and also by trapping #N/A with ISNA. Drop me a line at
fisherofsouls AT (not cold !)mail.com if you want to know more.

Regards

Nick