VLOOKUP between lists with Property as key

fadalian

Hello and thank you all for your time and help. Is there a way to set up a kind of vlookup between 2 lists, using one Property as the common line between them? I have created the same special key within both lists using 2 codes, that is a property. Now, I just want to do an old fashion excel´s vlookup between those two lists using this special key I have created,... but it is not working at all. Is it even possible? I know I can work things out in a module, but I would much rather prefer to do this in the list. Thanks in advance!!

JaredDolich

@fadalian 

Yes, this can be done. First I'll give you the answer but then you need to promise to build this using D.I.S.C.O. methodology. Use system modules in particular. 

 

In list one you need the property you want to lookup. I added one called "Color List 1"

 

In list two you have to have the code of list 1 to lookup. Add these properties and formulas.

 

In list 2, you can now see that List 1 color shows up.

 

Now that we've built this using lists, you should delete it and use system modules. Using lists like this is inefficient and goes against the PLANS methodology because it's very hard to change and not accessible using formulas. Not good for modeling and UX.

 

Good luck @fadalian !! 

Let us know if this idea works for you.

einas.ibrahim

Hey @fadalian 

 

Before I answer, I'd like to say to you - and anyone who would be reading - that you really should move away from thinking in Excel to implement in Anaplan. your starting point should be what results do I need to get? what are some of the functions or capabilities of Anaplan that I can use to achieve these results?
No worries, we all fell in that trap at one time or another.

 

Now to the core issue,

 

I assume you have 2 lists List A and List B and you need to get the matching item in List A (A.1) from List B (B.1).

The first step you did by creating the same code for both lists is a good first step, however, don't use List properties, use the Lists Native Code.

So now we have something like this:
List AList B

 

The goal is to return List item B.1 as the matching item for A.1.

1. As you can see the codes for the matching list items are identical
2. In a module dimensioned by List A we will return all the matching items in List B. 
   

The steps are as follows:

1. Retrieve the List A item using the formula ITEM()
2. Now that you have the Item, you can get the CODE for that item using the formula CODE()
3. Now we have the Code of the item in List A which is the same code as the equivalent item in List B, we can retrieve the List B item that matches that in List A using the formula FINDITEM()

This is the blueprint for your reference 

Depending on the rest of your application build, this module would be a separate System module.

 

I hope this was clear. if you have any questions we are here.

 

happy Planning

 

fadalian

Thanks for your time and help!!
What you are suggesting sounds great, and works for another issue I had.

Beste regards.

fadalian

Oh man. That totally works. You are a genius!
So, you are recommending not to work things in lists like this, and I will proceed as you suggest.
Thanks again and best regards.

einas.ibrahim

Oh @fadalian,

 

Thank you very much for your kind words.
I recommend you read the Planual. It contains a lot of Anaplan best practices such as using a system module instead of  list properties.

 

Also, our friend @Misbah wrote a series of posts titled Anaplan Explained - Day x. I think he reached day 26 🤔

If you type Anaplan Explained in the Community search you will be able to find these posts. I recommend them because misbah explains the reason why something is a best practice.

 

Good luck and if you need anything else let me know by mentioning my name @einas.ibrahim in your post.