PDA

View Full Version : Excel Question



beeswax34
28-04-2007, 06:22 PM
I just need a formula that will do the following:

I have a large range of data in which are the following numbers (1,2,3,4,5,6,7 and blank). What I want is Excel to see 1 for example and in the cell next to all the 1's enter cricket and for 2 rugby and for 3 tennis and so on.

So I would like Excel to do the this basically:

Value Sport
1
cricket
3 tennis
5 swimming
6 athletics
4 badminton
-(blank cell) fencing

johnd
28-04-2007, 06:44 PM
Make a table with all the options in it - two columns with the numbers down the left and sports in the second column.

Where your data is, have the numbers down the first column and use a vlookup in the second to find the sport in the table you made.

=vlookup(cell ref for the number to the left, cell range of your table, 2)

The last number tells the vlookup to go across 2 columns.

Not sure if this makes sense?

beeswax34
28-04-2007, 07:00 PM
Thanks johnd. The problem is that there are many other columns of data on one side and I cannot creat a table just with the 2 columns. I did get the VLOOKUP part though. Thanks.

godfather
28-04-2007, 08:15 PM
The two columns in the array required and referenced in VLOOKUP can be on another worksheet in the workbook though well away from the data you want to display.. You only need that 1 column beside your 1,2,3 etc to display the result based on the VLOOKUP formula which would reside in that single column.

beeswax34
29-04-2007, 12:21 AM
Would johnd's formula automatically insert the name of the sport in the next column according to the number or is there another formula to be used after this one?

TeejayR
29-04-2007, 09:28 AM
Yes John's formula should automatically populate the sport for you.

The only problem with the formula is that if you had a number for a sport that didn't exist, say 11 was entered instead of 1, then this would be treated as a the previous number and return the value for that number.

You can get around this by appending the false condition to the formula
=vlookup(cell ref for the number to the left, cell range of your table, 2,false)
, this returns #N/A which alerts you to check the data.

beeswax34
29-04-2007, 03:31 PM
Well, I still cant do it. I made a table like John said that has numbers from 1-8 in the first column and the assigned sports in the next column, well away from the main database. I then go to the column in the main worksheet where I want all the names populated and do VLOOKUP. This works, but only for one row. So if I had 2 next to the formula, it will return Rugby but only for that one cell, it wont do it for all the 2's in that whole column.

What am I doing wrong. The numbers are not sorted in ascending order so is that it?

zcc
29-04-2007, 04:13 PM
Use access instead...

Mike
29-04-2007, 04:39 PM
Well, I still cant do it. I made a table like John said that has numbers from 1-8 in the first column and the assigned sports in the next column, well away from the main database. I then go to the column in the main worksheet where I want all the names populated and do VLOOKUP. This works, but only for one row. So if I had 2 next to the formula, it will return Rugby but only for that one cell, it wont do it for all the 2's in that whole column.

What am I doing wrong. The numbers are not sorted in ascending order so is that it?Put your lookup array in another worksheet, and name the array (Select the entire lookup array, Insert > Name > Define, then give the array a name - in this example I'll call it "Sport").

In your main worksheet, in the column next to where your numbers are (I'll use column A for the numbers in this example, so in column B) put:

=vlookup(a1, Sport, 2, false)
Naming the array gives it an absolute reference, which means when you copy your formula down all the cells in the column, the lookup should still refer to the same array reference.

If you want to see an example, PM (http://www.pressf1.co.nz/private.php?do=newpm&u=8474) me with your email address, and I'll send you an example XLS file.

HTH,
Mike.

beeswax34
29-04-2007, 05:57 PM
Thank you so much Mike!!:D. Looks like defining the array really helped. And thanks to johnd, TJR, zcc and godfather for responding.:D:D:D