VLOOKUP finds first few matches and then stops

[Fonzie]

I will try not to overwhelm with excess information, but I am trying to fill certain rows with data from another sheet, based off what is filled in in another cell. The 3 cells next to Species should be filled from the cell next to Species (in this case, “Coward”, “Increased Will” and “Natural Armor” are filled based off the user writing “Armadillo” above it)

The situation now looks like this:

Formula: =VLOOKUP($H$2,$'<data>'.$A$1:$'<data>'.$D$144,2)

This works fine for a few rows

But fails after Bat

The data table looks like this

This currently works for A1 through A5 (it works for “Armadillo” through “Vampire Bat”, but keeps showing data from A3 (“Bat”) after on data from A6 and onward (“Bear” and onward).

I tried removing the cache folder under ~/.config/libreoffice/4, but that didn’t solve the problem either.

Can anyone help me find the solution to this? Is it my syntax? Is it another issue? Thank you in advance!

P.S.: If it helps anything; here is the file in question: ironclaw_fillable_charactersheet.ods

[anon73440385]

Hello,

Libre Office Help for function VLOOKUP() states:

…If Sorted is set to FALSE or zero, an exact match must be found…

Since you require an exact match in an unsorted list you need to use:

in cell H6: =VLOOKUP($H$2;$data.$A$1:$AD$144;2;0)
in cell H7: =VLOOKUP($H$2;$data.$A$1:$AD$144;3;0)
in cell H8: =VLOOKUP($H$2;$data.$A$1:$AD$144;4;0)

Here is your modified version: ironclaq_fillable_charactersheet_mod.ods

PS: If you want that to work without option 0, you need column A in sheet data to be sorted (Personally I use VLOOKUP() without option 0 only in case of real performance issues or in case of matching a value range)

Hope that helps.

[Fonzie]

That does indeed solve the issue! Thank you very much! (and thank you for even providing a modified spreadsheet!)