Vlookup help (I think)

[rharder]

In Sheet1 I have select sic codes in Column A, in Sheet2 I have an entire sic code list in column A and the descriptions in column B.

I need a formula or macro that looks at all the sic codes in Sheet2, Column A, then puts the appropriate description in Column B of sheet 1. For example:

Sheet1
A B
5432
8765
9876

Sheet2
A B
3456 Autos, compact
5432 Motorcycles
6789 Farm Equpment
8765 Furniture
9876 Chairs

I need the correct descriptions to wind up in column B of sheet 1. Any help?

Thanks in advance.

 

[mvptomlinson]

Hi Rharder,

Yes, VLOOKUP is the easiest in this case.

=VLOOKUP(A1,Sheet2!A:B,2,0)

 

[Robert Mika]

or
=IF(ISERROR(INDEX(Sheet2!$A$1:$B$5,MATCH(Sheet1!A2,Sheet2!$A$1:$A$5,0),2)),"",INDEX(Sheet2!$A$1:$B$5,MATCH(Sheet1!A2,Sheet2!$A$1:$A$5,0),2))
in case there in no match or numbers are in random order.

 

[mvptomlinson]

in case there in no match

Then wrap the more efficient VLOOKUP in an IF or IFERROR (Excel 2007) formula, e.g.

=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),"")

=IF(ISERROR(VLOOKUP(A2,Sheet2!A:B,2,0)),"",VLOOKUP(A2,Sheet2!A:B,2,0))

INDEX/MATCH functions are necessary if looking up a value in one column and returning a value from a column to the left of that value. VLOOKUP is more efficient when doing a normal left-to-right lookup.

or numbers are in random order.

The fourth argument in a VLOOKUP function can be 1 or 0 (TRUE or FALSE). When it's 0 (FALSE), it doesn't matter what order the search range is, it will always look for an exact match.

 

[rharder]

So sorry. You are right, I neglected to tell you that I'm using Excel 2003. Any help is greatly appreciated.

 

[mvptomlinson]

Use any of the formulas provided in our responses above except the IFERROR function, which is only in 2007 or later.

 

[rharder]

Worked very well. You guys are great. Many thanks.

 

[Robert Mika]

Hi mvptomlinson,

My way was just another way..
Thanks for clarification- you are right about VLOOKUP.

But is worth to add that referring to whole columns(A:B) is also not necessary as this will only slow down calculation (especially in X2007).