Tech Support Guy banner
Status
Not open for further replies.
1 - 20 of 79 Posts

·
Registered
Joined
·
41 Posts
Discussion Starter · #1 ·
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.
 

·
Registered
Joined
·
7,837 Posts
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.
 

·
Registered
Joined
·
41 Posts
Discussion Starter · #3 ·
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.
 

·
Registered
Joined
·
7,837 Posts
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.
 

·
Registered
Joined
·
7,837 Posts
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))
 

·
Registered
Joined
·
7,837 Posts
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

·
Registered
Joined
·
41 Posts
Discussion Starter · #14 ·
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.
 

·
Registered
Joined
·
7,837 Posts
It always helps to actually post the design of the real worksheet, not just a concept...if you had, you would have had your answer yesterday. :)
Here you go, with the data in Column G.
The one error I found was that Ehrenberg had a space preceding or following it, so I copied it over from Sheet2.
You might want to consider using a drop-down list on Sheet1 so you don't get errors of that sort.
 

Attachments

·
Registered
Joined
·
7,837 Posts
No need to be sorry - just letting you know that you will get faster answers if you ask directly for what you need.
As for what to do in your workbook, the formula works as it was in the last file I uploaded.
See attached.
 

Attachments

1 - 20 of 79 Posts
Status
Not open for further replies.
Top