Pulling data from another workbook using one common criteria

[anilos81]

Here is my situation:

I have a spreadhseet with a list of ids and phone numbers.

I have another spreadsheet with further contact info, listed by ID number.

I want to combine all of this info on one sheet, using the id number to match the records.

Is there a formula I can use for Excel to find the id number in the second workbook & pull that entire record over and add it to the record I have with only the phone number and ID? Or even putting all of the combined info in a separate sheet would be fine.

I am fairly good at Excel, but not a pro by any means, so can you please post easy to intermediate help?

Thanks!
Amanda

 

[NBVC]

Have you looked into the Vlookup function?

 

[anilos81]

Have you looked into the Vlookup function?

No I haven't. How does that work? Like I said, I am not a pro!

 

[NBVC]

Excel's help files on Vlookup is very good. You should really check there first.

From Excel help:
"Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table...

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) "

use a 0 or FALSE for range_lookup if you want an exact match.


So for your situation:

=Vlookup(ID Number from Sheet 1,Table in Sheet 2, where ID is left most column, Column number to extract from,0)

If you need further assistance, then tell me the location of all your data, including respective sheet names, table ranges and other cell references.

 

[anilos81]

Excel's help files on Vlookup is very good. You should really check there first.

From Excel help:
"Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table...

Syntax

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) "

use a 0 or FALSE for range_lookup if you want an exact match.


So for your situation:

=Vlookup(ID Number from Sheet 1,Table in Sheet 2, where ID is left most column, Column number to extract from,0)

If you need further assistance, then tell me the location of all your data, including respective sheet names, table ranges and other cell references.

Thank you. Here are my specifics. In "AtlantaGeorgiaListings.xls" I have only column A and that contains all of the agent IDS. Each appears only once.

In "AtlantaGeorgiaAgents.xls" I have many columns, but column A contains the agent IDs that correspond to the other workbook.

I want all the data found in ""AtlantaGeorgiaAgents.xls" to be pulled into "AtlantaGeorgiaListings.xls" and fill columns B - ?
next to the matching ID number.
Amanda

 

[NBVC]

I have assumed that your information starts in cell A2 in each of the sheets. If not, adjust where I have $A2 and $A$2 to match your location of the top left most ID.

In column B2 of AtlantaGeorgiaListings.xls (assuming B2 is where you want to pull the first bit of data to), type

=VLOOKUP($A2,[AtlantaGeorgiaAgents.xls]Sheet1'!$A$2:$Z$100,2,0)

Please adjust the range $A$2:$Z$100 to match your range in the Agents sheet.

Then copy this formula down your list (within Column B) and then copy all the formulas in Column B across all your other columns (equivalent to number of columns from the Agents file you need to pull)

Hope this helps.

 

[anilos81]

I have assumed that your information starts in cell A2 in each of the sheets. If not, adjust where I have $A2 and $A$2 to match your location of the top left most ID.

In column B2 of AtlantaGeorgiaListings.xls (assuming B2 is where you want to pull the first bit of data to), type

=VLOOKUP($A2,[AtlantaGeorgiaAgents.xls]Sheet1'!$A$2:$Z$100,2,0)

Please adjust the range $A$2:$Z$100 to match your range in the Agents sheet.

Then copy this formula down your list (within Column B) and then copy all the formulas in Column B across all your other columns (equivalent to number of columns from the Agents file you need to pull)

Hope this helps.

Thanks! This worked all except the copying the formula across the columns. It changed the A2 => B2 => C2 etc... (but did not change the 2 =>3 =>.4 etc. I needed the A2 to remain constant whiile the 2 count upward all the way to about 25. Its okay though, I went ahead and changed the 2 manually and then copied the entire row down through my sheet and it was fine.

Thanks again!

Amanda

 

[NBVC]

If you copied my formula correctly you should not have had to change the cell references manually.

I placed a $ in front of the A to "freeze" that column. So no matter where you placed the formula it should have always had that $A. I did not place a $ in front of the number part of the cell ref on purpose so that the number would increment as you copied down.


Sorry, the part I did mess up though is that I told you to copy over the columns, but actually you would have had to change was the col_index_num to match the column number you wanted to pull from.

 

[anilos81]

If you copied my formula correctly you should not have had to change the cell references manually.

I placed a $ in front of the A to "freeze" that column. So no matter where you placed the formula it should have always had that $A. I did not place a $ in front of the number part of the cell ref on purpose so that the number would increment as you copied down.

The only thing you would have had to change was the col_index_num to match the column number you wanted to pull from.

I'm sorry, I didn't mean to be unclear. It did work when I copied downward. The problem was that when I coped across the A2 did change, I am not sure why... I copied the formula you gave me and only changed the ranges and the column number I wanted to pull from. Everything went perfectly when I was copying downward, no big deal, thanks! It did the job and that's all that matters.

Amanda