Vlookup & Offset #Value Error

[dreen]

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' :

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

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

 

[dreen]

I would also like to cross-reference my other post on: How to do a Vlookup with offset or small array

 

[braindiesel]

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]

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]

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]

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]

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]

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]

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

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]

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

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!