Problem with OFFSET

[cata2200]

Hello

I have a problem with one OFFSET formula.

I am defining a dynamic array, called "Vessels_Details", as:

=OFFSET('Monitoring Vessels'!$B$5, 0, 0, COUNTA('Monitoring Vessels'!$B$5:$B$200),50), thru Name Manager.

In a second sheet ("Lists") I am using this newly created array to locate few more details, with the formula:

=IF($B6="", "", IF(COUNTIF(Vessels_Details, Lists!B58)<>1, "Error", VLOOKUP(Lists!$B58, Vessels_Details, 3, FALSE)))

Strange, the value returned is not the same with the expected one, calculated with:

=IF(B6="", "", IF(COUNTIF('Monitoring Vessels'!$B$5:$BG$108, B6)<>1, "Error", VLOOKUP(B6, 'Monitoring Vessels'!$B$5:$BG$108, 3, FALSE)))

where the "Vessels_Details" is actually replaced be the entire array.

Where do I do a mistake?

Many thanks for assistance,
Catalin

 

[Marcelo Branco]

Hi Catalin,

I think the 50 in this formula

=OFFSET('Monitoring Vessels'!$B$5, 0, 0, COUNTA('Monitoring Vessels'!$B$5:$B$200),50)
is not correct.

It results B:AY not B:BG as in your other formula
=IF(B6="", "", IF(COUNTIF('Monitoring Vessels'!$B$5:$BG$108, B6)<>1, "Error", VLOOKUP(B6, 'Monitoring Vessels'!$B$5:$BG$108, 3, FALSE)))

Try 58 instead of 50

M.

 

[cata2200]

Mario

Many thanks, but, normally, I am inside the array, since I am checking for the third column info.

However, after your message, without any modification, I have applied the formula:

=VLOOKUP(B6, Vessels_Details, 3, FALSE)

and ... it is OK.

Please note I didn't mofidy the array formula.

Regards
Catalin

 

[Marcelo Branco]

Good that solved your problem.

I noticed that the formula was getting the 3rd column, but i thought the problem was in the COUNTIF bit - different ranges, different counts...

M.