Help please! I don't even know how to title this.

[j3nnifers]

Hi there, I tried to search for an answer before posting this thread, but I realized I don't even know what to search for.

I have two separate workseets. Worksheet1 has a list of names and their respective industry codes. Wksht2 has a full compilation of all possible industry codes and their respective industry names/descriptions.

What I'd like to do is create a new column in Wksht1 and create a formula that will take the value of the industry code for each line, call it X. Then take X and search it among Wksht2 and find a match. Then take X and its corresponding industry name/description and return that industry name/desciption value to Wksht1.

Any ideas or suggestions? I'd be super duper grateful for any help you can offer!

[hindsight]

What you want to do can be accomplished very easily with a vlookup formula.

If your worksheet is set-up in the following manner:

Wksht 1
A B
Name Code
1 XXX ZZZ
2 XXX ZZZ
3 XXX ZZZ

Wksht2
A B
1 ZZZ Banking
2 ZZZ Retail
3 ZZZ Software

in Column "C" on Wkst1 type the following

=vlookup(b1,Sheet2!A1:B3,2,0)

Adjust the Sheet2! for the actual range your data is in, copy and paste the formula all the way down your list.

You can also get a better explanation of vlookup's by looking on the front page of this site or google "how do I use vlookup in excel."

Best,

JB

[Ron Coderre]

I think a VLOOKUP would give you what you want:

=vlookup(Wksht1 cell with the code to find, Wksht 2 two-column range with codes and desc, 2, 0)

Of course, those aren't the actual first two function arguments...they're just descriptions of what you should put there.

The 2 tells the function to pull values from the 2nd column of the lookup list.
the 0 tells it to only use EXACT matches.

Does that help?

Regards,
Ron

[j3nnifers]

All right, I've just finished reading through and understanding the replies. (Which I am most thankful for!)

I understand up to the part where I ask it to look up the value I specify, but how does the formula capture and return the corresponding industry name after it's found it?

I've tried and entered it into the worksheet and it returns: #N/A

This is what I have:
=VLOOKUP(W10,'NAICS sort'!A1:F2382,2,0)

w10 = the cell to look up
'NAICS sort'!A1:F2382 = is the name of the worksheet and it's range

and 2 being the column to lookup in the sheet labeled NAICS sort
and 0 for exact match

What am I doing wrong?

[Ron Coderre]

VLOOKUP works the way you'd use a phone book.

You look up a name down the left column.
When you find the right name, you take the phone number to the right of it.

Now if you were asked to grab the first person with the last name of Smith, you'd be using an APPROXIMATE match.

However, if you were looking for Bartholomew Smith....you'd use an EXACT match.

The VLOOKUP works similarly, except that your lookup list can have more than 2 columns....for instance: Name, Phone, Zip Code. So you have to tell it which column to use and whether to use an approximate match or not.

Hence: =Vlookup(item to find, range to look in, column to use, Approx OK?)

One more thing....If you use an EXACT match and the function can't find it...it returns #NA.

Does that help?

Regards,
Ron

[j3nnifers]

so then I changed my formula to:

=vlookup(w10,'NAICS sort'!A1:F2382, 6, 0)

as opposed to looking in column 2 it now looks in column 6 where industry name is located.

and it still returns #N/A................................

[Ron Coderre]

If you're sure the value in w10 is on your list, then check to see if there's a mismatch in data type. For instance, the number 10 formatted to look like 010 is NOT the same as the text 010 (which you might enter by starting with an apostrophe before the 010 part).

Also, check for trailing spaces: "xyz " versus "xyz". They make a difference.

Am I helping yet?

Regards,
Ron

[j3nnifers]

Ron, you're definitely a big help and I appreciate any that you can offer.

I'm sure there does exist a value in the cell W10 because I'm creating my vlookup formula in the cell W9. I've gone and made sure that every value that I'm looking for is formatted as a number. I've even sorted both lists in ascending order.

I really wish I knew where the error was happening........

[Ron Coderre]

OK...something's getting out of hand, so it's time to work with a prototype.

Copy SOME of your data into a new workbook, structured the same as the original and try to make that work. For instance, change one of the codes to "DAVE" on the lookup list and in the cell your trying to match and try to get that to work.

See if that helps you isolate the problem.

Regards,
Ron

[j3nnifers]

Okay! It works now.
The error was really stupid...the column I was searching and the column I wanted to search from had different indentations--centered and right.

Wow, I'm so glad it works because I was really about to go mad.

Thanks, Ron, for your patience and help!

[Ron Coderre]

You're welcome. I'm glad you solved the problem.

Ron