The formula you are looking for is called VLOOKUP VLOOKUP will, as it's name suggests, lookup up a value in a vertical column. The trick to using this formula is that you need a table to lookup your values. If this is big in terms of numbers of possible combinations you may think of using a desktop database like Access. But within Excel build a simple table, keep it simple and build it on the same sheet, it can always be off to the right and out of sight (and protected from users if required). the table is your index number and the test associated with the index so 1 work 2 gym 3 shop in two columns, lets say index is in col D and the test in col E. You must keep this table up to date, when you create another index, add it to this list (the table). Now you have something to lookup. In cell A1 type the index 1 in cell A2 type the index 2 in cell A3 type the index 3 In cell A2, place the formula =IF(ISERROR(VLOOKUP(A1,D1:E3,2)), "",VLOOKUP(A1,D1:E3,2)) Which translates as: Lookup the value from cell A1, in the table defined by D1:E3, and when you find the value [in the left most column of the table which is sorted AZ] return the value in col 2 of the table from the same row, otherwise leave the cell blank. Cell A2 now holds the correct value of "work". Drag the formula in A2 down, or use a macro to update. If you use the tool provided when you click "fx" by the formula bar it will guide you through writing the formula. SECOND ALTERNATIVE If column A is always grouped by number and you want to work without a lookup table you can use a simple if statement for a brute force solution A B 1 Huey 1 =IF(A2=A1,B1) 1 =IF(A3=A2,B2) 2 Duey 2 =IF(A5=A4,B4) 2 =IF(A6=A5,B5) 2 =IF(A7=A6,B6) 3 Luey 3 =IF(A8=A8,B8) 3 =IF(A9=A9,B9) Each new group of numbers will need a new text seed at the first entry, thus the advantage of the lookup table.

The formula you are looking for is called VLOOKUP VLOOKUP will, as it's name suggests, lookup up a value in a vertical column. The trick to using this formula is that you need a table to lookup your values. If this is big in terms of numbers of possible combinations you may think of using a desktop database like Access. But within Excel build a simple table, keep it simple and build it on the same sheet, it can always be off to the right and out of sight (and protected from users if required). the table is your index number and the test associated with the index so 1 work 2 gym 3 shop in two columns, lets say index is in col D and the test in col E. You must keep this table up to date, when you create another index, add it to this list (the table). Now you have something to lookup. In cell A1 type the index 1 in cell A2 type the index 2 in cell A3 type the index 3 In cell A2, place the formula =IF(ISERROR(VLOOKUP(A1,D1:E3,2)), "",VLOOKUP(A1,D1:E3,2)) Which translates as: Lookup the value from cell A1, in the table defined by D1:E3, and when you find the value [in the left most column of the table which is sorted AZ] return the value in col 2 of the table from the same row, otherwise leave the cell blank. Cell A2 now holds the correct value of "work". Drag the formula in A2 down, or use a macro to update. If you use the tool provided when you click "fx" by the formula bar it will guide you through writing the formula. SECOND ALTERNATIVE If column A is always grouped by number and you want to work without a lookup table you can use a simple if statement for a brute force solution A B 1 Huey 1 =IF(A2=A1,B1) 1 =IF(A3=A2,B2) 2 Duey 2 =IF(A5=A4,B4) 2 =IF(A6=A5,B5) 2 =IF(A7=A6,B6) 3 Luey 3 =IF(A8=A8,B8) 3 =IF(A9=A9,B9) Each new group of numbers will need a new text seed at the first entry, thus the advantage of the lookup table.