Formula to Find same data in a second workbook and bring associated data

**nzginga** · 09-21-2011, 02:13 PM

Hi there,

First time I've posted here but have been using this forum for a long time to help me figure things out based on other peoples experience.

However I've come across something I can't seem to find in the forum, maybe I'm not using the right terms.

I have two separate workbooks that I am trying to bring together a little bit. One has the some information, the other has some just because of the other people who have been working on it.

What I am trying to do is get a value from one worksheet onto another based on if the data in two specific columns match.

So in words (without a formula) If Sheet 1A1 can be found in A1:A100 enter B1

Is there a formula that can do this?

I look forward to seeing the responses

**DBY** · 09-21-2011, 02:32 PM

Hi
Do you mean to check the value of workbook 1, sheet 1, cell A1 against the same cell in workbook 2 and if they match return the value to cell B1 sheet 1 of workbook 1 and so on down column B?

**nzginga** · 09-21-2011, 02:40 PM

Wow that was fast,

I mean if Cell A1 (in sheet 1) can be found in '[Sheet 2.xlsx]Totals'!$A$1:$A$100 return the value from '[Sheet 2.xlsx]Totals'!$B$1:$B$100 that corresponds to the cell that was found to match cell A1. So if a4 was found to match i would want the value from b4 and so on.

Any thoughts?

**DBY** · 09-21-2011, 03:39 PM

You could try the following formula typed in Sheet 1 cell B1, which would return the value from the Totals sheet column B that matches the value you have in Sheet 1 cell A1.

=VLOOKUP(A1,[WorkbookName]Totals!A$1:B$100,2,0)

The only problems is that the VLOOKUP function finds the first match and returns a value from that. So if the column you are searching has duplicate values it only finds the first in the list. At the moment I don't know a way round that. I am using Excel 2003 and I'm not sure if there's anything in later versions that works differently.