Solved

How To Use VLOOKUP or INDEX Across Two Excel Files

Posted on 2010-11-08
4
331 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 81

Accepted Solution

by:
byundt earned 500 total points
ID: 34086621
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
ID: 34086676
Beat me to it Brad, but here's the files anyway....

FileA.xlsx
0
 

Author Closing Comment

by:asc2010
ID: 34086941
byundt:

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

Expert Comment

by:byundt
ID: 34086974
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

828 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