How to get rid of the Vlookup Path in a Formula

Hi Guys, I have a Lookup to another Workbook using Index Match. Is there a way I can shorten my formula using just the name of the spreadsheet instead of its whole path?


eg. instead of =IF(ISNA(INDEX('V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\12_2013 Recs\BS\[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$AL,MATCH(TRUE,INDEX('V:\Treasury Finance Controls\Ledger v SS Recs\Recs - Murex_GBO\2013 Recs\12_2013 Recs\BS\[Murex BS rec breaks - 031213.xlsm]R

to "=IF(ISNA(INDEX('[Murex BS rec breaks]Rec'!$B:AL,Match(True,Index('[Murex BS Rec Breaks]" thus getting rid of the whole path of the spreadsheet in the formula
JustincutAsked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
If you have Excel 2007 or later, you can use IFERROR.

The formula I gave you on the previous thread would be reduced to:

=IFERROR(INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$AL,MATCH(TRUE,INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$B=$B556,0),0),AD$1),IF($J556="","NEW",IFERROR(INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$C:$AL,MATCH(TRUE,INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$C:$C=$C556,0),0),AD$1-1),"NEW")))
0
 
andrew_manCommented:
You can find somewhere to store the path

Then, use indirect(cell).  If you dun know how to do, I can teach you step by step.
0
 
andrew_manCommented:
My suggestion is keep the link at another cell rather than in formula.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
NBVCCommented:
INDIRECT() won't work if the OP closes the workbook at any point.  Also, the OP is looking up a value in a range... you will need to use VLOOKUP, INDEX/MATCH or some UDF to achieve the lookup....
0
 
andrew_manCommented:
I think just update the cell and open the workbook.
0
 
Rob HensonFinance AnalystCommented:
As NB_VC says INDIRECT doesn't work when the source workbook is not open. However, there is another option, download the MULTIFUNC add-in and one option in that is INDIRECT.EXT which allows INDIRECT to work when source workbook is closed.

I can't remember the source of the Add-In but a Go Ogle search would probably find it.

Thanks
Rob H
0
 
NBVCCommented:
Rob, you are talking about Morefunc.xll addin I think.  That add-in is not reliable for versions after 2003 as far as I recall.
0
 
Rob HensonFinance AnalystCommented:
Indeed, I didn't think MULTIFUNC was right when I was typing it!!

Another option would be to create a Named Range that refers to a range in an external workbook, you can then just use the Range Name instead of the full file path.

Thanks
Rob H
0
 
JustincutAuthor Commented:
If I use a Named Range called "BS Rec", for example.How would this formula look like? =IF(ISNA(INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$AL,MATCH(TRUE,INDEX('[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$B=$B556,0),0),AD$1? Also, is there not a more simplistic Index Match formula?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.