Looking up Excel data when the key column (one with reference values) is not leftmost

4
votes

I have one Excel spreadsheet which has the following columns: Surname / First Name / e-mail / gender / info 1 / info 2 / info 3 / info 4 This is a master spreasheet with everyone's details.

I have another spreadsheet containing just the e-mail addresses of a handful of people. All of them are in the master spreadsheet. The columns are as follows: Surname / First Name / e-mail / Project / gender / info 2 / info 3 / info 4 / info 1

Is there a way of asking Excel to look in the e-mail column of the master spreadsheet and search for the e-mail mentioned in the other spreadsheet. When the matching e-mail is found, look up data in master spreadsheet for that person, and transpose them into appropriate columns in the other spreadsheet.

I'm happy to clarify anything that doesn't make much sense.

Cheers!

EDIT: changed title from "Importing data from another Excel spreadsheet based on specific values" to "Looking up Excel data when the key column (one with reference values) is not leftmost"

1
excel
Have you tried using the VLOOKUP() worksheet formula? - Tim Williams
He can’t – VLOOKUP expects the lookup key to be in the first column of the range it searches, which is not true in the cases of the Surname and First Name lookup. See my answer for a solution using LOOKUP. - kopischke
May I suggest changing the title to “Looking up Excel data when the key column is not leftmost”? That is an issue Excel is notoriously bad at handling, and that makes this thread interesting. - kopischke
Why - this is easily solved with a basic INDEX/MATCH? Any handling issues are here are user related, not inherent to Excel. - brettdj
Fair point, I jumped the gun (to paraphrase you :)) with “notoriously bad at handling”; “not offering an obvious solution to less advanced users” would have been closer to the mark. I still think it’s of interest to users wondering how to achieve that, and the new title will give it better visibility. - kopischke

1 Answers

2
votes

In your case, you want to perform a lookup on a data table where the key column is not the leftmost column – which means you cannot use Excel’s prime contender for such a lookup, the VLOOKUP formula. The following ways will work:

If the emails in the master spreadsheet are sorted in ascending order

Use Excel’s LOOKUP function. Assuming that in both your spreadsheets the “Surname” is column A and that you have named all columns in the correct order in your question, the lookup for Surname in row 1 would be:

=LOOKUP(C1,'[Master.xls]TableName'!$C$1:$C$100,'[Master.xls]TableName'!$A$1:$A$100)

with the other columns adjusted accordingly. The second parameter never changes ; the third takes whatever column index the values you want to lookup has in the master table (i.e. B for First Name, D for gender etc.).

Note this assumes your master spreadsheet as 100 rows of data to look up (adjust accordingly – LOOKUP does not work with entire columns, and you cannot use VLOOKUP, which does).

… and if they are not

Use a combination of INDEX and MATCHfunctions. Assuming the same layout as above, the lookup for Surname in row 1 would be:

=INDEX([Master.xls]TableName'!$A:$H,MATCH(C1,[Master.xls]TableName'!$C:$C,0),1)

Note this fails if the address is not matched exactly – be wary of capitalization differences, trailing and leading whitespace, and differing cell data formats.

Note for both variants

“Master.xls” and “TableName” are just placeholders for the sake of demonstration – they need to be replaced by the correct file and table names. The easiest way to create inter-file references is to open both files and fill the formula per point and click – Excel will create the correct references for you. However, be warned that linking to another file inside a formula relies on that file not changing its path, ever.