How to replace number with specific text?

G

garfiola

New Member
Joined
Aug 8, 2011
Messages
41
I have 2 columns

first is category id and second is category

so for example

2 is mango
3 is strawberry
4 is banana
..
..
..
..
550 is cherry

How to put category name in the category id column instead of number?

If I select category id and just do copy and replace, it replaces every cell that has 2 number, for example 21, 200... and it messes up the whole thing

What formula should I use for this?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
How to put category name in the category id column instead of number?
Click to expand...

You wouldn't because they're two disparate data types.

If you mean that you want to be able to select a Category Name and retrieve the Category ID, then you can use INDEX/MATCH:


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category ID</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2</td><td style=";">Mango</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">3</td><td style=";">Strawberry</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">4</td><td style=";">Bannana</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">550</td><td style=";">Cherry</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">VLOOKUP - ID</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category ID</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category Name</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Strawberry</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">INDEX/MATCH - Name</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category ID</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category Name</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Strawberry</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,A2:B5,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A13</th><td style="text-align:left">=INDEX(<font color="Blue">A2:A5,MATCH(<font color="Red">B13,B2:B5,0</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />

HTH,
 
Upvote 0
Smitty said:
You wouldn't because they're two disparate data types.

If you mean that you want to be able to select a Category Name and retrieve the Category ID, then you can use INDEX/MATCH:


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category ID</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">2</td><td style=";">Mango</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">3</td><td style=";">Strawberry</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">4</td><td style=";">Bannana</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">550</td><td style=";">Cherry</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">VLOOKUP - ID</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category ID</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category Name</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Strawberry</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">INDEX/MATCH - Name</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category ID</td><td style="color: #FFFFFF;background-color: #4F81BD;;">Category Name</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">3</td><td style="text-align: center;;">Strawberry</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B9</th><td style="text-align:left">=VLOOKUP(<font color="Blue">A9,A2:B5,2,FALSE</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A13</th><td style="text-align:left">=INDEX(<font color="Blue">A2:A5,MATCH(<font color="Red">B13,B2:B5,0</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />

HTH,
Click to expand...

thank you i think i will use only vlookup

you are great
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Data Validation question
Replies
4
Views
329
Peter_SSs
Peter_SSs
W
  • Solved
Return values in comma separated format when specific text is found in a string
2
Replies
10
Views
808
Cubist
Cubist
B
  • Question Question
How to add specific values in a table?
Replies
5
Views
328
Peter_SSs
Peter_SSs
M
  • Question Question
Token replace between two files with dynamic refresh
Replies
1
Views
80
Rijnsent
Rijnsent
D
  • Question Question
How to use If Statement or Vlookup when comparing lists of names
2
Replies
13
Views
474
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,537
Messages
6,179,405
Members
452,911
Latest member
a_barila

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top