match columns and associated data
 

Hi,

I have a sheet where columns A-D are given, sorted ascending to col A.
I have data in another sheet A and B where col A may match a value in
sheet 1,col A. I need to do this for sheets that may contain up to 3000
rows. The sheets may or may not contain the same numbers of rows. I need
to match if possible the BarCd located in sheet 2 and bring along the
Location column with it and place it in the same row as the matching
value in sheet 1.


Thanks in advance

Ray

Example:Sheet 1
BarCd - UII - Nomen
27122 - LDW53W9LD2217 - LCHR GREN RIF M76 MC
27123 - LDW53W9LD2218 - LCHR GREN RIF M76 MC
27126 - LDW53W9L157974 - PISTOL PYRO AN-M8 15
27127 - LDW53W9LE068750 - PISTOL PYRO AN-M8 1 5
29235 - LDW53W9L8036268 - TELEPHONE CELL W/CAM
29238 - LDW53W9L8790941 - TELEPHONE CELLULAR


Sheet 2
BarCd - Location
29235 - 13
29240 - 13
29243 - 13
29247 - 13
29250 - 13
29251 - 13
29239 - 13A
29245 - 2074
29238 - 2532
29246 - 3149
29249 - 3325
27122 - 3327
27123 - 3327
27126 - 3327
27127 - 3327


--
RayB
------------------------------------------------------------------------
RayB's Profile: http://www.excelforum.com/member.php...o&userid=33499
View this thread: http://www.excelforum.com/showthread...hreadid=562560

match columns and associated data
 

RayB,

Where you want the location to be for A2 in Sheet!1 enter,

=IF(ISNA(VLOOKUP(A2,Sheet2!$A$2:$B$3000,2,FALSE)), "",VLOOKUP(A2,Sheet2!$A$2:$B$3000,2,FALSE))

This will return a blank if the value in A2 is not found.



HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=562560

match columns and associated data
 

A vlookup should work

=VLOOKUP(A2,Sheet2!$A$1:$B$3000,2,FALSE) Returns N/A# if not in list

or

=IF(ISNA(VLOOKUP(A2,Sheet2!$A$1:$B$3000,2,FALSE)), "Not in
list",VLOOKUP(A2,Sheet2!$A$1:$B$3000,2,FALSE))

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562560