Index Match question?

Posted on 2013-12-05
Last Modified: 2013-12-11
Hi Guys, I have this Vlookup which is not working due to the 256 character limit. I understand Index Match has no 256 limit, buy will it work looking up another file? Here's my current Vlookup:

=IF(ISNA(VLOOKUP($B556,'[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$AL,AD$1,FALSE)),IF($J556="","NEW",IF(ISNA(VLOOKUP($C556,'[Murex BS rec breaks - 031213.xlsm]Rec'!$C:$AL,AD$1-1,FALSE)),"NEW",VLOOKUP($C556,'[Murex BS rec breaks - 031213.xlsm]Rec'!$C:$AL,AD$1-1,FALSE))),VLOOKUP($B556,'[Murex BS rec breaks - 031213.xlsm]Rec'!$B:$AL,AD$1,FALSE))
Question by:Justincut
  • 2
  • 2
LVL 23

Expert Comment

ID: 39698055
That would be translated to:

=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)),IF($J556="","NEW",IF(ISNA(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",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))),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))

But remember, that this is an array formula that you don't need to confirm with CSE keys, but you should make the lookup ranges defined sizes, not large (whole column ranges).

Author Comment

ID: 39698076
What are CSE keys?
LVL 23

Expert Comment

ID: 39698110
Sorry,  that means CTRL+SHIFT+ENTER (these keys must used together to confirm array formulas)... but in the formula I provided it is not necessary.

Accepted Solution

andrew_man earned 500 total points
ID: 39698119
1) The file name is too long!
2) False to 0

Author Comment

ID: 39703144
Hi Andy, is there a more simplistic Index Match formula then this one:=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?

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

Suggested Solutions

Title # Comments Views Activity dynamic formulas in runtime 11 63
Hiding column macro 10 28
Easy Excel formula needed 4 27
Lock selected columns without protecting worksheet 13 28
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

867 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

23 Experts available now in Live!

Get 1:1 Help Now