Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# How to get rid of the Vlookup Path in a Formula

Posted on 2013-12-05
Medium Priority
496 Views
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
0
Question by:Justincut
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2
• +1

LVL 4

Expert Comment

ID: 39698232
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

LVL 23

Accepted Solution

NBVC earned 2000 total points
ID: 39698376
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

LVL 4

Expert Comment

ID: 39698393
My suggestion is keep the link at another cell rather than in formula.
0

LVL 23

Expert Comment

ID: 39698403
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

LVL 4

Expert Comment

ID: 39698419
I think just update the cell and open the workbook.
0

LVL 33

Expert Comment

ID: 39698517
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

LVL 23

Expert Comment

ID: 39698525
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

LVL 33

Expert Comment

ID: 39698538
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

Author Comment

ID: 39703150
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month12 days, 2 hours left to enroll