INDEX/MATCH using two files

**Mr.rE** · 07-27-2017, 03:29 PM

Just found this forum, and glad it exist.

I'm very new at this level of Excel (a few days), so forgive me if I have written this incorrectly.
Task:
Type a alphanumeric code in cell 'F11' on (Sample Workbook 1.xlsx) then find the same alphanumeric code in file (Sample Workbook 2 Project-Wide.xlsx) column U, then return only the alphanumeric code if it begins with "M" in column C.

My argument:
=INDEX('C:\Formulas\[Sample Workbook 2 Project-Wide1.xlsx]COMP'!$C:$C,MATCH(F11,'C:\Formulas\[Sample Workbook 2 Project-Wide1.xlsx]COMP'!$U:$U,0))

Sample Files:
Attached

**avk** · 07-27-2017, 04:34 PM

Your sample workbook 2 project-wide not open. Kindly reattach proper file.

**Tsjallie** · 07-27-2017, 04:43 PM

Hi and welcome to this forum.
Firstly, why did you give the second attachment the extension "xlsx" while it has a macro in it and should be "xlsm"?
That's why it's not opening.

In Sample Workbook 1.xlsx:
- Select cell F11 and add a Named Range with this formula and name it TagName:

Formula:

Please Login or Register  to view this content.

This will do the lookup
- In cell F11 than enter this formula:

Formula:

Please Login or Register  to view this content.

This will do the selection and cuts off the numeric part of the found code.
The formula refers to the named range which makes it more readable.

Do notice that the workbook the lookup is done in needs to be opened.
There are methods to access unopened workbooks in Excel 2016, not sure if they are available in Excel 2013

**Mr.rE** · 07-27-2017, 05:36 PM

Sorry for uploading a bad file.
This should work.

**Mr.rE** · 07-27-2017, 05:52 PM

Thanks, I'll try it 'Asap' when I'm back in the office.

**Tsjallie** · 07-28-2017, 05:32 AM

Noticed an error in my response. You should not put the formula in F11 as that hold the search value already.
While testing I've put it in G11. But that can be any other cell as long as it's on the same row as the named formula is referring to.

**Mr.rE** · 07-28-2017, 08:14 AM

Tsjallie; I'm substituting 'F11' with 'E11'. You mentioned, "Add a Named Range and name it TagName", I'm not sure what cell to add the named range?
I added the 'Index' statement to cell E11, but where do I add the 'IF' statement? Or do I combine with the 'Index Match' statement?
Thank you for your help.

**Tsjallie** · 07-28-2017, 03:08 PM

A Named Range is entered in the Name Manager. This how:

Make sure a cell in row 11 is selected. This is because the formula in the Named Range is referring to the cell in column F and row 11.
Select the Formula tab in the ribbon and in there select Name Manager.
In the Name Manager select New and enter "TagName" as the Name and in the Refers to box enter this formula:
Formula:
```
Please Login or Register  to view this content.
```
I used the second workbook you uploaded, so workbook name and sheet name differ from the formula I posted earlier.
Now select cell E11 and enter this formula there:
Formula:
```
Please Login or Register  to view this content.
```

**Mr.rE** · 07-28-2017, 04:10 PM

Thank you, I understand now.
I tried it, but 'E11' will only return back the letter 'M', no other characters.

This is the closest I've seen it work yet, thanks. What should I change in the 'IF' statement?

**Tsjallie** · 07-28-2017, 04:51 PM

It's only showing "M" because the found tagname has just "M" as it's character part.
No need to change the IF-statement.

**Mr.rE** · 07-28-2017, 05:54 PM

Originally Posted by Tsjallie

It's only showing "M" because the found tagname has just "M" as it's character part.
No need to change the IF-statement.

Ok, so is there a way to get the rest of the information to the right of 'M' to show in each cell?

**Tsjallie** · 07-29-2017, 04:58 AM

is there a way to get the rest of the information to the right of 'M' to show in each cell?

You can remove the Substitute-part from the formula in E11

Formula:

Please Login or Register  to view this content.

This will return the whole tagname.

BTW I noticed that there are multiple tagnames associated withNCS150 and there all different.
Need to do something about that?

**Mr.rE** · 07-31-2017, 01:03 PM

Perfect...it works! Thanks Tsjallie

**Tsjallie** · 07-31-2017, 04:15 PM

Glad I could help.
Thx for the rep

INDEX/MATCH using two files

LinkBack

Thread Tools

Rate This Thread

Display

INDEX/MATCH using two files

Re: VERY new at this. INDEX - MATCH, using two files.

Re: INDEX/MATCH using two files

Re: VERY new at this. INDEX - MATCH, using two files.

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Re: INDEX/MATCH using two files

Thread Information

Users Browsing this Thread

Similar Threads

index-match between two huge files

[SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays

[SOLVED] Index Match Issue linked with Other files

Problems with multi-criteria Index/Match. LOOKUP works. Index/Match, SUMPRODUCT,ARRAY fail

Index, match, dynamic and connected without sorting helper column non-adjacent index match

[SOLVED] Concatenated INDEX MATCH formula that won't give error that can index match 2-4 strings

Index/Match from 2 sources of files

Bookmarks

Bookmarks

Posting Permissions