Solved

How to get rid of the Vlookup Path in a Formula

Posted on 2013-12-05
9
428 Views
Last Modified: 2013-12-11
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
Comment
Question by:Justincut
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 4

Expert Comment

by:andrew_man
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

by:
NBVC earned 500 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

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

Expert Comment

by:NBVC
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 4

Expert Comment

by:andrew_man
ID: 39698419
I think just update the cell and open the workbook.
0
 
LVL 32

Expert Comment

by:Rob Henson
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

by:NBVC
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 32

Expert Comment

by:Rob Henson
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

by:Justincut
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now