Search Excel data when key column (one with reference values) is not left

Question

I have one Excel spreadsheet that has the following columns: Last Name / First Name / Email / Gender / Information 1 / info 2 / info 3 / info 4 This is a master sprite with all the details.

I have another spreadsheet containing only the email addresses of a few people. All of them are in the main table. The columns are: Surname / First name / Email / Project / Gender / Information 2 / info 3 / info 4 / info 1

Is there a way to request Excel to search the email column of the main spreadsheet and search the email specified in another spreadsheet. When a suitable email is found, find the data in the main spreadsheet for that person and transfer them to the corresponding columns in another table.

I am happy to clarify everything that does not make much sense.

Hooray!

EDIT: changed name from "Import data from another Excel table based on specific values" to "Search Excel data when the key column (one with reference values) is not left"

Answer 1

In your case, you want to search in the data table where the key column is not the leftmost column, which means that you cannot use Excels pre-challenger for such a search, the VLOOKUP formula. The following methods will work:

If emails in the main spreadsheet are sorted in ascending order

Use the Excels LOOKUP function. Assuming that in both tables “Last Name” is column A and that you named all the columns in the correct order in your question, the search for the last name in row 1 will be as follows:

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

and the remaining columns are adjusted accordingly. The second parameter never changes; the third one takes any column index, the values ​​you want to find are in the main table (i.e. B for the name, D for the floor, etc.).

Note that this assumes that your main spreadsheet is used as 100 rows of data to search (adjust accordingly - LOOKUP does not work with all columns, and you cannot use VLOOKUP , which does).

... and if they don’t

Use a combination of INDEX and MATCH functions. Assuming the same layout as above, the last name search in line 1 will look like this:

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

Please note that this does not work if the address does not match exactly - be careful with differences in capitalization, trailing and leading spaces and different cell data formats.

Note for both options

"Master.xls" and "TableName" are just placeholders for demonstration - they need to be replaced with the correct file and table names. The easiest way to create links between files is to open both files and fill out the formula for each point and click - Excel will create the right links for you. However, it should be warned that linking to another file inside the formula uses this file without changing its path.