# Vlook up value with diffrent allocations

1. ## Vlook up value with diffrent allocations

Hi all,
Just bumped into this forum today. I hope you can help me to handle an excel allocation,
which I couldn't manage due to my infinite stupidity
Well here's the case - I have two sheets:
- First one - which I called "Look up value" - containing four columns:

Employee Nos Dimension Code 1 Dimension Code 2 Dimension Code 3

EMP_01 - - -
EMP_02 - - -
EMP_03 - - -

- Second one - which I called "Allocation" - containing Employee Nos; Dimension Codes; Dimension Values

Employee Nos Dimension Codes Dimension Values
EMP_01 Dimension Code 1 x
EMP_01 Dimension Code 2 y
EMP_01 Dimension Code 3 z

So my task is to fill in the correct dimension value in the relevant column in the first sheet using the information from the second one.
Can you help me?

2. ## Re: Vlook up value with diffrent allocations

probably bet to use index and match.

can you upload a sample book for us to work on. it would make it easier

3. ## Re: Vlook up value with diffrent allocations

Sure! And because I couldn't manage to attach it from here - I've loaded it in a dropbox -hope It'll do
https://www.dropbox.com/sh/8xizioizu...nYA/Book1.xlsx
Sorry for that

4. ## Re: Vlook up value with diffrent allocations

i would hazard a guess at something like this if this is the way you have listed your information.

but then again i dont know 100% (by the way this is an array formula and as such needs to be entered with Control+Shift+Enter not just Enter

Book1.xlsx

5. ## Re: Vlook up value with diffrent allocations

cool i was right here you go

Book1 (6).xlsx

6. ## Re: Vlook up value with diffrent allocations

Thank you VERY MUCH !!! I' ll hit the star a million times... ! ...and then I'll spend the rest of my life decoding the formula you've written :DDD Once again - thanks a lot!

7. ## Re: Vlook up value with diffrent allocations

ok if you wanna know how this works.

``Please Login or Register  to view this content.``
firstly you set up what field you want to index (in this case the dimension part
(in this case C2:C10)

then you get it to find where a match occours (its an array formula do to the ract that you have to add 2 cells togther (i put a bit extra in incase words overlap)
so match(A1&"||"&B1,
means match(EMP_01||Dimension 1

then you set up where it seaches so by adding the to feilds again you create a list of results

then excel picks the exact match and returns that row and the index uses that to give you an answer

im not sure how clear that is but i hope it helps a bit

8. ## Re: Vlook up value with diffrent allocations

here is your book with it broken down a bit to help i hope

Book1 (6).xlsx

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1