Vlookup & Offset #Value Error

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
I have two workbooks, one where I am doing the Vlookup and the other contains the table I need to retrieve the information from.

I am trying to basically Vlookup the table found in the other workbook '[Database_IRR 200-2S.xlsm]Changes'! below row 3 as sometimes the lookup retrieves information from the second row and I don't want those parameters from the table. The reason I can't start the Vlookup at the third row of the table is because my lookup table can have data entered into it (shifting the rows down) and this shifts the absolute references of the table array within my Vlookup formula (see example below):

For example if I enter, =VLOOKUP("TEST 1_SL.1V.L",'[Database_IRR 200-2S.xlsm]Changes'!$A$3:$FZ$10000,ROWS($A$1:$A6),FALSE) and a new entry/row is added (So another Test 1
_SL.1V.L in column A for example) to the workbook that has the lookup table '[Database_IRR 200-2S.xlsm]Changes' then my formula becomes =VLOOKUP("TEST 1_SL.1V.L",OFFSET(('[Database_IRR 200-2S.xlsm]Changes'!$A$1:$FZ$10000),2,0),ROWS($A$4:$A6),FALSE). **Notice how $A$3 --> $A$4**

To handle this I am attempting to do a Vlookup from row 1 and offsetting it by 2 rows down, because the absolute references ($A$1 below) don't seem to change when a new row is added to the workbook whenever the absoulate references are inside an OFFSET function (So the $A$1 doesn't change to $A$2 when a new data entry is added (a new row) to [Database_IRR 200-2S.xlsm]Changes'). Now this formula below only works when I have the other workbook open where the lookup table is coming from, that is [Database_IRR 200-2S.xlsm]Changes'! , if I close it then my Vlookup formula is returning a #VALUE error

Here is the formula I am using:

=VLOOKUP('Operation base Q'!$H$5,OFFSET(('[Database_IRR 200-2S.xlsm]Changes'!$A$1:$FZ$1000),2,0),ROWS($A$1:$A6),FALSE)

Here is a screenshot of the lookup data from the file path '
[Database_IRR 200-2S.xlsm]Changes' :


Test 1 Setup sheet.JPG


Another ALTERNATIVE solution I am trying to use is an Index formula with a Small Array (Hold Ctrl+Shift+Enter whenever entering an array). The array makes my sheet very slow and it won't grab the "first" lookup value (as in the Third row and below) IF the second row isn't filled in with the lookup value (see screenshot below of the workbook '[Database_IRR 200-2S.xlsm]Changes'! where the second row is different as I am looking up TEST 1, so my formula is returning the fourth row when I want it to return the third row).

Test 2 Setup sheet.JPG


Here is the formula I am using for this alternative:

=INDEX(INDEX('[Database_IRR 200-2S.xlsm]Changes'!$A$2:$FZ$10001,0,ROWS($1:6)),SMALL(IF('Operation base Q'!$H$5='[Database_IRR 200-2S.xlsm]Changes'!$A:$A,ROW('[Database_IRR 200-2S.xlsm]Changes'!$A:$A),""),1))
 
Last edited:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Perhaps changing your OFFSET
OFFSET(('[Database_IRR 200-2S.xlsm]Changes'!$A$1,2,0,<HEIGHT>,<WIDTH>)
Make the height 10000 and the width how ever many columns A:FZ is
 

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
Perhaps changing your OFFSET
OFFSET(('[Database_IRR 200-2S.xlsm]Changes'!$A$1,2,0,<HEIGHT>,<WIDTH>)
Make the height 10000 and the width how ever many columns A:FZ is

Hello,

I tried to change the offset to include the height and width, so after your input my first solution became:

=VLOOKUP('Operation base Q'!$H$5,OFFSET(('[Database_IRR 200-2S.xlsm]Changes'!$A$1),2,0,10000,300),ROWS($A$1:$A6),FALSE)


I still seem to be getting a #Value error when closing the other workbook where the parameters are being retrieved (the Database_IRR 200).
 

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52

ADVERTISEMENT

I have two workbooks, one where I am doing the Vlookup and the other contains the table I need to retrieve the information from.

I am trying to basically Vlookup the table found in the other workbook '[Database_IRR 200-2S.xlsm]Changes'! below row 3 as sometimes the lookup retrieves information from the second row and I don't want those parameters from the table. The reason I can't start the Vlookup at the third row of the table is because my lookup table can have data entered into it (shifting the rows down) and this shifts the absolute references of the table array within my Vlookup formula (see example below):

For example if I enter, =VLOOKUP("TEST 1_SL.1V.L",'[Database_IRR 200-2S.xlsm]Changes'!$A$3:$FZ$10000,ROWS($A$1:$A6),FALSE) and a new entry/row is added (So another Test 1
_SL.1V.L in column A for example) to the workbook that has the lookup table '[Database_IRR 200-2S.xlsm]Changes' then my formula becomes =VLOOKUP("TEST 1_SL.1V.L",OFFSET(('[Database_IRR 200-2S.xlsm]Changes'!$A$1:$FZ$10000),2,0),ROWS($A$4:$A6),FALSE). **Notice how $A$3 --> $A$4**

To handle this I am attempting to do a Vlookup from row 1 and offsetting it by 2 rows down, because the absolute references ($A$1 below) don't seem to change when a new row is added to the workbook whenever the absoulate references are inside an OFFSET function (So the $A$1 doesn't change to $A$2 when a new data entry is added (a new row) to [Database_IRR 200-2S.xlsm]Changes'). Now this formula below only works when I have the other workbook open where the lookup table is coming from, that is [Database_IRR 200-2S.xlsm]Changes'! , if I close it then my Vlookup formula is returning a #VALUE error

Here is the formula I am using:

=VLOOKUP('Operation base Q'!$H$5,OFFSET(('[Database_IRR 200-2S.xlsm]Changes'!$A$1:$FZ$1000),2,0),ROWS($A$1:$A6),FALSE)

Here is a screenshot of the lookup data from the file path '
[Database_IRR 200-2S.xlsm]Changes' :


View attachment 17790

Another ALTERNATIVE solution I am trying to use is an Index formula with a Small Array (Hold Ctrl+Shift+Enter whenever entering an array). The array makes my sheet very slow and it won't grab the "first" lookup value (as in the Third row and below) IF the second row isn't filled in with the lookup value (see screenshot below of the workbook '[Database_IRR 200-2S.xlsm]Changes'! where the second row is different as I am looking up TEST 1, so my formula is returning the fourth row when I want it to return the third row).

View attachment 17786

Here is the formula I am using for this alternative:

=INDEX(INDEX('[Database_IRR 200-2S.xlsm]Changes'!$A$2:$FZ$10001,0,ROWS($1:6)),SMALL(IF('Operation base Q'!$H$5='[Database_IRR 200-2S.xlsm]Changes'!$A:$A,ROW('[Database_IRR 200-2S.xlsm]Changes'!$A:$A),""),1))

I typed a piece of my explanation incorrectly, the second paragraph should be (sorry for the inconvenience the edit button expired):

For example if I enter, =VLOOKUP("TEST 1_SL.1V.L",'[Database_IRR 200-2S.xlsm]Changes'!$A$3:$FZ$10000,ROWS($A$1:$A6),FALSE) and a new entry/row is added (So another Test 1_SL.1V.L in column A for example) to the workbook that has the lookup table '[Database_IRR 200-2S.xlsm]Changes' then my formula becomes =VLOOKUP("TEST 1_SL.1V.L",OFFSET(('[Database_IRR 200-2S.xlsm]Changes'!$A$4:$FZ$10000),2,0),ROWS($A$1:$A6),FALSE). **Notice how $A$3 --> $A$4**
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Ah,
You cannot reference a closed workbook with OFFSET, thus your value errror.
If you are already looking at a huge range, maybe set it up as a named Range just for simplicity.
Since you cannot change the size of the range while closed you will be accurate.

You could add some events to the IRR file like Before Open, Before close, Before save and then redfine the named range... then it should be accurate enough.
Does that make sense to you or do we need to go further
 

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52

ADVERTISEMENT

Ah,
You cannot reference a closed workbook with OFFSET, thus your value errror.
If you are already looking at a huge range, maybe set it up as a named Range just for simplicity.
Since you cannot change the size of the range while closed you will be accurate.

You could add some events to the IRR file like Before Open, Before close, Before save and then redfine the named range... then it should be accurate enough.
Does that make sense to you or do we need to go further

Not being able to reference a closed workbook with OFFSET is definitely correct, that didn't cross my mind at all! My only issue is that the workbook containing the parameters I am using for the Vlookup has a macro that I coded, where if you click on any column, it automatically forces your selection to become the respective cell in Column A of that row and gives it a named range "MyRange" this is so the second row can always have a Vlookup depending on whatever I have selected (this is to compare old parameters with new ones).

Basically, if I select cell "E7", the active cell becomes "A7" and "A7" gets a temporary named range as long as "A7" remains in the active row I have selected (So if I selected B7, H7, S7, etc. it will always go to A7 and keep it's named range). Does my explanation sort of make sense, just to give you a sense of what is also going on with the workbook that I am pulling the Vlookup from.
 

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
I tried using a Double INDEX & MATCH combination but when I add a new line to the other workbook, the absolute reference changes as well, here is what I mean:

This is another formula I am trying to use (third alternative):

=INDEX(INDEX('[Database_IRR 200-2S.xlsm]Changes'!$A$3:$FZ$10000,0,ROWS($1:6)),MATCH('Operation base Q'!$H$5,'[Database_IRR 200-2S.xlsm]Changes'!$A$3:$A$1000,0))

but whenever I insert a new row into the Database_IRR 200-2s workbook, the absolute references change like this (see in bold):

=INDEX(INDEX('[Database_IRR 200-2S.xlsm]Changes'!$A$4:$FZ$10001,0,ROWS($1:6)),MATCH('Operation base Q'!$H$5,'[Database_IRR 200-2S.xlsm]Changes'!$A$4:$A$1001,0))
 

braindiesel

Well-known Member
Joined
Mar 16, 2009
Messages
560
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
If your Database is 10000 lines and you insert a row, then it SHOULD go to 10001...
If this is a concern, then my guess is that you made it 10,000 to capture any additional rows without falling outside your named range... basically playing it safe.
If I am correct, be careful how safe you are playing it... making it too large will slow your sheet down and lead to "calculation lag"

Lets pretend your database goes down to row 9000

You could have a named range called myRange... refers to '[Database_IRR 200-2S.xlsm]Changes'!$A$3:myBot

myBot
refers to '[Database_IRR 200-2S.xlsm]Changes'!$F$9100 (I gave myself 100 extra rows to be safe but not too safe.... you can adjust your buffer in a moment

Then create an event marco on Before open, Before save, before close

VBA Code:
Private Sub Workbook_Open()
 Range("A1").Select' choose a column that has data in every relevant row
 If ActiveCell.Offset(1, 0).Value <> "" Then Selection.End(xlDown).Select
 ActiveCell.Offset(50, 0).Name = "myBot"'if you want 1000 safety rows, then offset(1000,0)
End Sub

As a result, all input rows will bump your range bottom down a row, but it will redefine on next open
You can add it on another trigger if you prefer
 

dreen

Board Regular
Joined
Nov 20, 2019
Messages
52
If your Database is 10000 lines and you insert a row, then it SHOULD go to 10001...
If this is a concern, then my guess is that you made it 10,000 to capture any additional rows without falling outside your named range... basically playing it safe.
If I am correct, be careful how safe you are playing it... making it too large will slow your sheet down and lead to "calculation lag"

Lets pretend your database goes down to row 9000

You could have a named range called myRange... refers to '[Database_IRR 200-2S.xlsm]Changes'!$A$3:myBot

myBot
refers to '[Database_IRR 200-2S.xlsm]Changes'!$F$9100 (I gave myself 100 extra rows to be safe but not too safe.... you can adjust your buffer in a moment

Then create an event marco on Before open, Before save, before close

VBA Code:
Private Sub Workbook_Open()
Range("A1").Select' choose a column that has data in every relevant row
If ActiveCell.Offset(1, 0).Value <> "" Then Selection.End(xlDown).Select
ActiveCell.Offset(50, 0).Name = "myBot"'if you want 1000 safety rows, then offset(1000,0)
End Sub

As a result, all input rows will bump your range bottom down a row, but it will redefine on next open
You can add it on another trigger if you prefer
Thank you for the suggestion!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,574
Messages
5,637,159
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top