can anyone help with a formula for excel

25 pts.
Tags:
Excel 2007
Microsoft Excel
Microsoft Excel formulas
I have Excel 2007. There is one sheet with 2 columns of data. Column one has a series of numbers (e.g, 111,22,3333,etc). Column 2 has a series of terms (e.g, work,gym,home,etc). My problem is: whereas there is a series of numbers in column 1, the terms in column 2 only appear on the first row of each of these column 1 series. 1 work 1 1 1 1 1 1 2 gym 2 2 2 2 2 3 shop 3 3 3 3 3 I want the terms to appear every time alongside each column 1 number. 1 work 1 work 1 work 1 work 1 work 1 work 1 work 2 gym 2 gym 2 gym 2 gym 2 gym 2 gym 3 shop 3 shop 3 shop 3 shop 3 shop 3 shop Can anyone supply a formula which I can put in column 3 which I can fill down to provide the desired effect?? Big thanks. Colwyn.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following