Searching separate sheet for a match, if a match is found insert several columns from sheet A to sheet B

[vayde]

Hi Guys,

I have a small list of numbers in one sheet and a huge database of numbers in another sheet. I'd like to transfer several columns of info from this database sheet into the smaller sheet everytime it finds a match in the numbers. Can you help?

 

[John Davis]

Hi Guys,

I have a small list of numbers in one sheet and a huge database of numbers in another sheet. I'd like to transfer several columns of info from this database sheet into the smaller sheet everytime it finds a match in the numbers. Can you help?

You could use VBA. You need to provide more information though. (ie. Location of the numbers for each sheet, Sheet names, which columns of information need too be transfered). Also a small sampling of data will get you a lot more responses.

 

[vayde]

Hi,

So the small sample of numbers are in Column I of Sheet A, in Sheet B we have much more numbers and lots of columns of information. Rather than having to find the number in the large sheet and copy the row into Sheet A I was wondering if there is a formula I can use that will search Sheet B for a number in cell I2, and if it finds a match then to copy that entire row in Sheet B and enter the contents into the empty columns to the right of I2 in Sheet A.

Is that more clear? Example below, except in sheet B the columns contain data that I'd like to pull into Sheet A if there's a match in ID. Sheet B has loads of IDs, Sheet A only has a small sample that I'd like to pull information for.

Sheet A:

<TABLE style="WIDTH: 464pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=616><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" span=8 width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20 width=77>ID</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77>Column 1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77>Column 2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77>Column 3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77>etc.</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20 align=right>123456789</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR></TBODY></TABLE>

 

[John Davis]

Hi,

So the small sample of numbers are in Column I of Sheet A, in Sheet B we have much more numbers and lots of columns of information. Rather than having to find the number in the large sheet and copy the row into Sheet A I was wondering if there is a formula I can use that will search Sheet B for a number in cell I2, and if it finds a match then to copy that entire row in Sheet B and enter the contents into the empty columns to the right of I2 in Sheet A.

Is that more clear? Example below, except in sheet B the columns contain data that I'd like to pull into Sheet A if there's a match in ID. Sheet B has loads of IDs, Sheet A only has a small sample that I'd like to pull information for.

Sheet A:

<TABLE style="WIDTH: 464pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=616><COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" span=8 width=77><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20 width=77>ID</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77>Column 1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77>Column 2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77>Column 3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77>etc.</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 58pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=77></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20 align=right>123456789</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3"></TD></TR></TBODY></TABLE>

Read up on the VLOOKUP functions, or INDEX/MATCH functions. It seems that you may be able to apply those. If you want code, I can try and help you.

 

[vayde]

Thanks John, I managed to do it with VLOOKUP.

Just another point, it's returning a zero for when it finds a blank field in the database, is there a way I can return a blank instead of a zero?

I did the following, unfortunately I need a separate formula for each column but at least it works:

Column 1:

=VLOOKUP(H2,'Account Raw Data'!A1:U15823,2,FALSE)

Column 2:

=VLOOKUP(H2,'Account Raw Data'!A1:U15823,3,FALSE)

etc.

 

[John Davis]

Thanks John, I managed to do it with VLOOKUP.

Just another point, it's returning a zero for when it finds a blank field in the database, is there a way I can return a blank instead of a zero?

I did the following, unfortunately I need a separate formula for each column but at least it works:

Column 1:

=VLOOKUP(H2,'Account Raw Data'!A1:U15823,2,FALSE)

Column 2:

=VLOOKUP(H2,'Account Raw Data'!A1:U15823,3,FALSE)

etc.

You're welcome. I'm working with Excel 2003. And for that you can use.

=IF(ISNA(VLOOKUP(H2,'Account Raw Data'!A1:U15823,2,FALSE)),"",VLOOKUP(H2,'Account Raw Data'!A1:U15823,2,FALSE))

to return a blank. For 2007 and later, I'm not sure that it will work.