Solved

How To Use VLOOKUP or INDEX Across Two Excel Files

Posted on 2010-11-08
4
321 Views
Last Modified: 2012-05-10
I have two Excel files, each with a list a US States, their abbreviations, and 3, non-adjacent columns of data I would like displayed in file A from file B.  I will attach the files I am working with.

In FileA, the columns should match the data in the corresponding columns in FileB based on the listed state.

FileA.xlsx
FileB.xlsm
0
Comment
Question by:asc2010
  • 2
4 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
For FileA cell D13, you could use a lookup formula like:
=VLOOKUP(B13,'[FileB.xlsm]United States'!$B$7:$U$58,MATCH(D$12,'[FileB.xlsm]United States'!$B$6:$U$6,0),FALSE)

If the lookup table in FileB contained fields for Size and Phase, you could copy the formula across.

Brad
0
 
LVL 6

Expert Comment

by:steverice
Comment Utility
Beat me to it Brad, but here's the files anyway....

FileA.xlsx
0
 

Author Closing Comment

by:asc2010
Comment Utility
byundt:

Awesome!!! Thank you so much, it works perfectly!
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
If you do copy the formula across, you'll need to add a $ for the state abbreviation column and the very beginning of the formula:
=VLOOKUP($B13,'[FileB.xlsm]United States'!$B$7:$U$58,MATCH(D$12,'[FileB.xlsm]United States'!$B$6:$U$6,0),FALSE)

The header label in cell D12 must match exactly one of the header labels in row 6 of FileB. If not, you'll get #N/A as the result of the formula.

Brad
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now