Relating one column to another

  • Thread starter Thread starter Alan Parker
  • Start date Start date
A

Alan Parker

Stupid newbie question coming up:

Let's say that in column A I have a series (not sequential) of numbers. In
column B I have a word that goes with that number.

In column C, I have numbers, which represent the same words as the number in
column a represents the word in column B.

In other words, I have:

Number Word Type:
1: Banana 2
2: Fruit
3: Sausage 4
4: Meat

I want to end up with

Number Word Type:
1: Banana Fruit
2: Fruit
3: Sausage Meat
4: Meat

Search and replace is out of the question, as there are 361 "things" under
about 140 "types".

The output is the only way I could get it from a database which is now no
longer there.

Is there any way of doing this in Excel? Thanks!
 
With data starting in A2 to C100,
Add a "helper" column D.

In D2, enter this formula:

=IF(C2<>"",INDEX($B$2:$B$100,MATCH(C2,$A$2:$A$100,0)),"")

And copy down as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Stupid newbie question coming up:

Let's say that in column A I have a series (not sequential) of numbers. In
column B I have a word that goes with that number.

In column C, I have numbers, which represent the same words as the number in
column a represents the word in column B.

In other words, I have:

Number Word Type:
1: Banana 2
2: Fruit
3: Sausage 4
4: Meat

I want to end up with

Number Word Type:
1: Banana Fruit
2: Fruit
3: Sausage Meat
4: Meat

Search and replace is out of the question, as there are 361 "things" under
about 140 "types".

The output is the only way I could get it from a database which is now no
longer there.

Is there any way of doing this in Excel? Thanks!
 
Add a help column for simplicity assume you use column D
assume all data starts in row 2 so in D2 put this formula

=IF(C2="","",VLOOKUP(C2,$A$2:$B$500,2,0))

copy down the formula alongside C, when done copy the new column,
do edit>paste special as values in place, delete column C
I noticed that the numbers in A have a colon after them,
is that the way it is? If so use

=IF(C2="","",VLOOKUP(C2&"*",$A$2:$B$500,2,0))
 
Peo Sjoblom said:
Add a help column for simplicity assume you use column D
assume all data starts in row 2 so in D2 put this formula

=IF(C2="","",VLOOKUP(C2,$A$2:$B$500,2,0))
Click to expand...

RagDyeR said:
With data starting in A2 to C100,
Add a "helper" column D.
In D2, enter this formula:
=IF(C2<>"",INDEX($B$2:$B$100,MATCH(C2,$A$2:$A$100,0)),"")
And copy down as needed.
Click to expand...

Many thanks to both of you - it worked fine! How do you work these things
out?? It would have taken me days!
I've been googling for this all morning, but it's very hard to find the
right keywords (got plenty of Excel tips sites in the bookmarks now,
though!)
 
Back
Top