Solved

Using something similar to Vlookup

  • 2 May 2023
  • 4 replies
  • 227 views

Userlevel 3
Badge +5
  • Newly Minted Pigmenteer
  • 16 replies

Hello, can you please help me with formula something similar to vlookup?

Trying to fill in the Division property using the Department code dimension.

Tried using “ITEM('PL GL Data'.Department,'Department Code'.'Department Name')”, but error says 

“ Error: Expressions are incompatible and cannot all be converted to Dimension (Department Code)”

 

Thanks in advance!

 

 

 

icon

Best answer by francois 3 May 2023, 10:17

View original

4 replies

Userlevel 6
Badge +14

Hi,

Your 'PL GL Data'.Department is not a text-format property here, but rather a dimension one.

Could you share the format of Department and Divison properties in your transaction?

Thanks!

Userlevel 3
Badge +5

Hi Francois, 

 

Sure, 

Currently, Department name is in text-format and trying to fill in the Division Final Property in Dimension format just created. In 2nd snapshot, you will see Department dimension with Department Name in text format and Division in dimension format. 

 

 

 

Userlevel 6
Badge +14

Ah, so it looks like you’re trying to chain properties then? I guess you’re trying to do two steps in one:

  • get the PL GL Data’s Department from its name
  • get the Division from the Department

This would be a formula like this:

ITEM('PL GL Data'.'Department Name','Department Code'.'Department Name').Division

I would advise on doing it in two steps, e.g. having a Department property in your PL GL Data transaction, like illustrated in your first message. Then you’d be able to use 'PL GL Data'.Department.Division very simply.

Using ITEM('PL GL Data'.'Department Name','Department Code'.'Department Name'), the result will be of Department format, so you can’t input that in a Division format property.

 

Hope this helps!

Userlevel 3
Badge +5

it worked. Thank you Francois!! 

 

So, because Department Name is in Text format, your below formula works best!

ITEM('PL GL Data'.'Department Name','Department Code'.'Department Name').Division

And learned another way to simplify is to change the department name from text format to dimension format first and use this

'PL GL Data'.Department.Division

 

Reply