asked on

Need to manipulate Excel sheets may be through VLookup

Hello. I have three Excel spreadsheets - A1.xlsx, D1.xlsx and I1.xlsx. The three spreadsheets will reside in the same folder. I am attaching the three example sheets.

I have to do the following steps:

From the "Sheet2" sheet in A1.xlsx, for each value in the HdrN column, we go into I1.xlsx and match with the HdrN column value. Whenever a match is found, we take all values for that row from columns A through H from I1.xlsx and paste them into A1.xlsx's columns G through N.

We then take the HdrC value from A1.xlsx and go to D1.xlsx's HdrN column and find that value and paste the columns G through N from A1.xlsx into columns I through P in D1.xlsx under the new header names.

NOTE: The new header names, HdrNew1 through HdrNew8, will already exist.

I know that I can use VLookup to do some of this stuff but I would like some solutions from some experts.

I am also attaching two "- After" spreadsheets to show what the final spreadsheets should look like.D1.xlsx
A1.xlsx I1.xlsx A1 After.xlsx D1 After.xlsx
Any responses would be greatly appreciated.

Rob Henson

VLOOKUP can be volatile in that it will re-calculate every time the sheet recalculates even if cells that it refers to haven't changed.

A less volatile approach would be using INDEX and MATCH

The first copy can be done with this formula in cell G2 of Sheet 2 of A1.xlsx:

=INDEX([I1.xlsx]Sheet1!$A:$H,MATCH($E2,[I1.xlsx]Sheet1!$A:$A,0),COLUMN()-6)

Copy across to other columns and down as required.

Second copy, there is no HdrN column in D1

nQuote

ASKER

Rob, thank you for your response. I got confused with the sheets. I'll upload the correct sheet with headers.

nQuote

ASKER

Hello Rob. Thank you again. I am seeing column B as the HdrN column in D1.xlsx. Are you not seeing it?

Rob Henson

Yep, apologies, my bad; I didn't scroll left!!

Rob Henson

Formula in cell I2 of D1.xlsx

=INDEX([A1.xlsx]Sheet2!$G:$N,MATCH($B2,[A1.xlsx]Sheet2!$C:$C,0),COLUMN()-8)

Copy across through P2 and down as required.

nQuote

ASKER

For I1.xlsx, A1.xlsx and D1.xlsx in the formula, do I put the full path in single quotes?

nQuote

ASKER

Hello Rob. I tried with this first:
=INDEX([I1.xlsx]Sheet1!$A:$H,MATCH($E2,[I1.xlsx]Sheet1!$A:$A,0),COLUMN()-6)

After I put in the value for cell G2, it pops up a File Explorer window for me to choose a file. How do I specify the full path of I1.xlsx, for example, C:\MyFolder1\Myfolder2\I1.xlsx?

Rob Henson

You can use the File Explorer window to browse to the correct file rather than manually entering the file path

If you have all of the related files open at the same time you can just use the file name, you don't need the path.

The syntax for the file reference is dependent on the file name having spaces or not.

If file name has spaces:
'[A A1.xlsx]Sheet2'!$G:$N Apostrophe before the opening [ and after the sheet name.

If no spaces:
[A1.xlsx]Sheet2!$G:$N No apostrophes

When you close the source file (file D1 is pulling data from file A1 so A1 is the source file) the path to that file will then show in the destination file.

nQuote

ASKER

The sheet name in A1.xlsx where I am trying to bring the value has spaces, something like, My sheet 2. How do I get around that?

nQuote

ASKER

This seems to be working for the first formula in my initial test. I'll have to test more. Is it possible to do wildcard or partial matches starting with the left hand side instead of full matches? So, for example, if first 10 characters of A1.xlsx (cell E2) matches first 10 characters of I1.xlsx(a cell in column A)?

Rob Henson

You can use:

=INDEX([I1.xlsx]Sheet1!$A:$H,MATCH(LEFT($E2,10)&"*",[I1.xlsx]Sheet1!$A:$A,0),COLUMN()-6)

nQuote

ASKER

Rob, thank you very much for your response. I used the 10 characters as an example. Is it possible to do something like "LIKE" that we can do in SQL? I'll research myself as well but I was wondering if you knew if something like that exists. For example, something like:

$E2 LIKE '%er%'

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nQuote

ASKER

Rob, thank you very much. Worked great.