[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Index Match question?

Posted on 2013-12-05
Medium Priority
165 Views
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:

0
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).
0

Author Comment

ID: 39698076
What are CSE keys?
0

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.
0

LVL 4

Accepted Solution

andrew_man earned 2000 total points
ID: 39698119
1) The file name is too long!
2) False to 0
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?
0

## Featured Post

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month19 days, 13 hours left to enroll