Nevermind I moved this to the product forum.
On Tuesday, January 1, 2013 8:18:32 AM UTC-6, Joel Barnes wrote:
>
> I created an array about 5c X 450r containing disparate index values but
> consistent numeric integer data in the 4 returning columns. It seems to me
> that vlookup fails to carry out the search once the array exceeds about 20
> rows. For example:
>
> =VLOOKUP("Widget 4",vlookup02!R204:V223,2)
>
> returns the correct value from cell S217, but when I spec the whole array
>
> =VLOOKUP("Widget 4",vlookup02!R4:V443,2)
>
> returns the value from cell S443, as if it's ignoring the search_criterion
>
> Troubleshooting really hasn't given me much insight. For example:
>
> =VLOOKUP("Widget 4",vlookup02!R204:V224,2)
>
> =VLOOKUP("Widget 4",vlookup02!R204:V225,2)
>
> =VLOOKUP("Widget 4",vlookup02!R203:V225,2)
>
> all continued to work when I changed my vlookup array one row at a time.
> But when I tried to expand the array by 2 rows
>
> =VLOOKUP("Widget 4",vlookup02!R203:V227,2)
>
> it failed again. It resumed working with an "undo", but then expanding one
> row
>
> =VLOOKUP("Widget 4",vlookup02!R203:V226,2)
>
> still failed.
> "Undo" again, then I expanded up one row, then down one row, and it seemed
> to like that.
>
> =VLOOKUP("Widget 4",vlookup02!R202:V226,2)
>
> That worked.
>
> Now, however, I find that even a small set will fail, seemingly at random.
> For example, I created a new array shifted down the sheet
>
> =VLOOKUP("Widget 4",vlookup02!R213:V243,2)
>
> and it is returning the value in S243 (last row in the array) instead of
> S214.
> Another value in that range works fine, for example
>
> =VLOOKUP("Item 5",vlookup02!R213:V243,2)
>
> finds its match in cell S228, but
>
> =VLOOKUP("Item 1",vlookup02!R213:V243,2)
>
> returns the error "Item 1 not found"
>
> What is going on?
>
