Nested VLookup

dave09

Board Regular
Joined
Oct 3, 2007
Messages
81
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!!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,885
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Oct 3, 2007
Messages
81
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,885
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Oct 3, 2007
Messages
81

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
10,885
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Oct 3, 2007
Messages
81

ADVERTISEMENT

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

Well-known Member
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
10,885
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Oct 3, 2007
Messages
81
That's it - it works!! Thanks very much Steve and Jason for your help!!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,329
Members
409,863
Latest member
stacy09
Top