Nested VLookup

[dave09]

I know this is really simple but I just cannot get it right!! Basically I have a common reference in 2 worksheets. This reference is in column G in one sheet (sheet 1), and column A in the second sheet (sheet 2).
I want to look up the number in sheet 2, and if column T is 'Yes', return the value in column B. However, if column T is "No" I need it to retrun the result in column X.
As I said, I know this is a dead simple nested V lookup but could do with some help - cheers!!

 

[jasonb75]

You could do it with vlookup or index, there are a few other methods but not so efficient.

=INDEX((Sheet2!B:B,Sheet2!X:X),MATCH(G2,Sheet2!A:A,0),,IF(INDEX(Sheet2!T:T,MATCH(G2,Sheet2!A:A,0))="Yes",1,2))

Will column T always be yes or no? the formula assumes that if it's not yes then it must be no, so any other values including blanks will give the wrong result.

 

[dave09]

Thanks Jason

I have tried this, however I am getting an error message stating that this file version cannot contain formulas that reference cells beyond a worksheet size of 256 columns or 65536 rows. Any ideas of a way round this?

I am hoping column T will always have a Yes or No incldued as there is a drop down filter included.

 

[jasonb75]

I can't recreate the error, have you made any corrections to the formula to match your data ranges? If yes, can you post the edited version.

Is this in excel 2003 or in a compatible workbook, i.e. one with a .xls file extension as opposed to .xlsx or .xlsm?

 

[dave09]

Thanks Jason
This is the actual formula I am using:

=index(('[2014 A4A Master Sheet September.xlsx]Sheet1'!$B:$B,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$X:$X),MATCH(G1610,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$A:$A,0),,if(index('[2014 A4A Master Sheet September.xlsx]Sheet1'!$T:$T,MATCH(G1610,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$A:$A,0))+"Yes",1,2))

It is saved in a Excel 97-2003 workbook.

 

[jasonb75]

That took me a while to spot

=index(('[2014 A4A Master Sheet September.xlsx]Sheet1'!$B:$B,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$X:$X),MATCH(G1610,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$A:$A,0),,if(index('[2014 A4A Master Sheet September.xlsx]Sheet1'!$T:$T,MATCH(G1610,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$A:$A,0))="Yes",1,2))

You had + at the end of the formula where it should be =

I'm hoping that was the cause of the error because I can't recreate it.

 

[dave09]

Sorry about that! However still getting the same error message. The file path for the worksheet includes [Compatibility Mode] for some reason - do you think that could have anything to do with it?

 

[steve the fish]

Its a compatibility problem. One worksheet is an xlsx and the other will be an xls. So the column lengths aren't the same size. In 2007 the spreadsheet could be over a million rows. In the past this was only 65000 or so. So your need to change your column references in the formula. eg $B$:$B$ to $B$1:$B$1000 . Match the 1000 part to match your data.

 

[jasonb75]

Finally managed to make it cause the error, I had created a test sheet in 97-03 format but it was still running in 07 format until I closed and reopened it.

My original thought was correct, I was just having trouble confirming it. The ranges in the xlsx workbook exceed the capability of excel 97-03 compatibility, B:B refers to a range of over 1 million rows where the limit is 65536.

The only way to make the formula work (that I can find) is to limit the ranges in the external references

=INDEX(('[2014 A4A Master Sheet September.xlsx]Sheet1'!$B$1:$B$65536,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$X$1:$X$65536),MATCH(G1610,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$A$1:$A$65536,0),,IF(INDEX('[2014 A4A Master Sheet September.xlsx]Sheet1'!$T$1:$T$65536,MATCH(G1610,'[2014 A4A Master Sheet September.xlsx]Sheet1'!$A$1:$A$65536,0))="Yes",1,2))

But naturally, if the data in the 2nd workbook extends beyond that range then it will not work correctly.

Beyond that, my only other suggestion is to save the compatible workbook as .xlsx or .xlsm so that you can use the full column references, again this could cause problems if anyone needs to open it in an old version of excel.

 

[dave09]

That's it - it works!! Thanks very much Steve and Jason for your help!!