Solved: Excel 2003- Matching text to a number.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Tonservices

Thread Starter
Joined
Sep 10, 2008
Messages
41
I am trying to match a location (Ogden, Utah)(Column I) in spread sheet 1 to spread sheet 2 in the same workbook. Once (Ogden Utah) is found in spread sheet 2 (Column B) I want (Column A) a three digit number returned back to spread sheet 1 (Column G). I would appreciate anyone help. Thanks.
 
Joined
Oct 20, 2004
Messages
7,837
First, welcome to the forum!
2 questions. First, would the location ever be duplicated (that is, Odgen in say, I2 and in I15) and second, what is the 3 digit number - where does it come from?
If you could post a copy of your file - with fake data if needed - that would help us. IF you find you can't post (using go advanced) then send me an email via my profile and I will post the file for you after responding to your email.
 

Tonservices

Thread Starter
Joined
Sep 10, 2008
Messages
41
Spread Sheet1 Spread Sheet 2 Column G Column I Column A Column B456 Ogden, Uah Site Number Location List012 Centerville Utah 123 Roy, Utah123 Roy, Utah 456 Ogden, Uah034 Salt Lake City, Utah 789 Layton, UtahMissing Ogden, Uah 012 Centerville Utah 034 Salt Lake City, Utah Q1 There are no duplicate site locations or site numbers in spread sheet 2. Q2 We assign a site number to a particular location. The site number will never change.
 

Tonservices

Thread Starter
Joined
Sep 10, 2008
Messages
41
How do I attach a file. I sure my last e-mail Excel 2003- Matching text to a number. Update 091708 would look better as a attached file. Sorry for the confusing mess.
 
Joined
Oct 20, 2004
Messages
7,837
Go to advanced, then down below the text area is a button for attaching.
If you find you have trouble doing it, email me via my profile and I will respond to you. Then you can email me the file and I will post it.
 

Tonservices

Thread Starter
Joined
Sep 10, 2008
Messages
41
I need to appologize, Where is the advance key. I don't know this program very well. Thanks for your patience.
 
Joined
Oct 20, 2004
Messages
7,837
Right below this text box - where you type in what you want to say - there are two buttons, Post Quick Reply and Go Advanced. Click on the Go Advanced button.
 

Tonservices

Thread Starter
Joined
Sep 10, 2008
Messages
41
To add an attachment - I use the paper clip? My paper clip has been disabled. I'm sorry for being completely lost at what I'm trying to do. Thanks.
 
Joined
Oct 20, 2004
Messages
7,837
Here you go. In Sheet 2, enter the values in column B. In column A, cell 2 (I assume you have titles in cells A1 and B1), enter this formula:

=IF(ISNA(INDEX(Sheet1!G:H,MATCH(B3,Sheet1!H:H,0),1)),"",INDEX(Sheet1!G:H,MATCH(B3,Sheet1!H:H,0),1))

It will then put the matching number from column G on Sheet 1 into column A on sheet 2. If there is not match, it leaves it blank, although if you want to have it say "Missing", just change it to

=IF(ISNA(INDEX(Sheet1!G:H,MATCH(B2,Sheet1!H:H,0),1)),"MISSING",INDEX(Sheet1!G:H,MATCH(B2,Sheet1!H:H,0),1))
 
Joined
Oct 20, 2004
Messages
7,837
I had looked at your sheet.
See the attached - after correcting spelling errors (can't match Uah and Utah) and changing a few numbers from text to numbers (in 2003 that are flagged by little green triangles in the corner of the cell), all I did was put the data in the correct columns and entered the formula in column A on Sheet 2.
 

Attachments

Tonservices

Thread Starter
Joined
Sep 10, 2008
Messages
41
I have recreated my actual spreadsheet. I can't send you a attachment explaining things differently. I will try and send you this copy tomorrow. Thanks.
 

Tonservices

Thread Starter
Joined
Sep 10, 2008
Messages
41
I want to know what you did to enable my attachment option? I have re-created my "actual" spreadsheet. I can't get this formula to work for my actual re-created spreadsheet. If you can then my problem is solved. Thanks.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top