Skip to Content

VLOOKUP in AO 2.6 in a formula column

Mar 12 at 05:54 PM


avatar image

I am working with AO 2.6. I am using a VLOOKUP in the workbook in a formula column (made in the AO query so it generated a Table Design entry).

I am using VLOOKUP to search for a value per IP line so e.g. cell Z25 and Z26 and Z27 etc. When I use the VLOOKUP witht $ sign for the column and not for the row the system removes this $ sign so it will always look for the Z25 value in the table area.

It seems dat when you use VLOOKUP in a Input ready query formula column the system always searches for the first line. Any of the members with the same experience?

I am using =VLOOKUP(Z25&AB23;SUBS7;2;FALSE)

Requirement is to search for a percentage based on the value in Z25 and Z26 and Z27 etc. Like I stated if I use $Z25 and use autocomplete the $ sign is removed and the VLOOKUP uses just Z25 fixed for all lines in the plannign WB. I also tried MATCH and INDEX but this has the same result.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Tammy Powlas
Mar 12 at 06:30 PM

Hello Jan - I did some recordings on VLOOKUP in this blog:

I also recommend patching to 2.6 SP1 - that is what I used in these recordings

Good luck


Show 3 Share
10 |10000 characters needed characters left characters exceeded

Hi Tammy thanks for your quick response! I checked the recordings before i used the function (I am using 2.6 SP1 also btw)

The issue is that I am using the VLOOKUP exactly the same way looking for a value on a planning line. The system acts a stated above;

So as an eample in planning line i have:

Line 25 Column Z Value e.g. Lemon

Line 26 Column Z Value e.g. KIWI

Line 27 Column Z Value e.g. Orange

The VLOOKUP (after autocompletion states =VLOOKUP(Z25&AB23;SUBS7;2;FALSE)) removes the $ sign and always searches for the value Lemon in the table SUBS7.

Seems strange behaviour IMO and different from your experience, am I correct?


Yes, experience is different than mine but I am not using planning. Could that be the difference here?


Is seems that it is Tammy, the thing is I am also using a Keyfigure as a LOOKUPVALUE instead of a Characteristic. I can try to find a characteristic in stead to see if this works although the KF does the job but only as the first line in the planning data. Perhaps the VLOOKUPVALUE should be in the first column not sure if this is the trick. Anyway again thanks for your prompt reaction I will test further and update the post as soon as I have addiotional information.