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?
