# not able to create a functioning VLOOKUP, MATCH, INDEX formula

1. ## not able to create a functioning VLOOKUP, MATCH, INDEX formula

Hello

I have 1 workbook with 2 sheets:
Sheet1 , column A with a list containing the unique document numbers.
Sheet2 has another list with these unique numbers in column A splitted with a location identifier (location can be 01 or 02) and their respective values (0,0 ; something,0 ; 0,something ; something, something are the 4 possible combinations).

Now, I want in Sheet1 2 additional columns: one for "location 01" and another column for "location 02".
I'm stuck in using VLOOKUP, MATCH, INDEX.

The formula in Sheet 1 column "location 01" should take the unique document number from Sheet1; lookup the document number in Sheet2 with location 01 and enter the value in that column.

The formula in Sheet 1 column "location 02" should take the unique document number from Sheet1; lookup the document number in Sheet2 with location 02 and enter the value in that column.

How can I achieve this?
Ebru.

2. ## Re: not able to create a functioning VLOOKUP, MATCH, INDEX formula

well not seeing your data this could in theory work.
=VLOOKUP(A3&" 01",Sheet2!\$A\$3:\$B\$12,2,FALSE)
so for example if you have location 123456 in A3 and 123456 01 in sheet2 A3 then it will return what is in column B.
for a better answer you probably ought to post a sample sheet.

3. ## Re: not able to create a functioning VLOOKUP, MATCH, INDEX formula

There are several possibilities. One is that the data types (numeric / text) are not of the same type. Appearances can be deceptive.

Without a representative workbook upload it is difficult to say.

If you are not familiar with how to do this:

To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• click “Close window”

4. ## Re: not able to create a functioning VLOOKUP, MATCH, INDEX formula

Hi Sambo kid!

Can you elaborate on the " 01" in
=VLOOKUP(A3&" 01",Sheet2!\$A\$3:\$B\$12,2,FALSE)

the formula should be in sheet1,
so take A3 of sheet1 then goto sheet2 vlookup value of A3 there which has 01 as location , then Sheet2!\$A\$3:\$B\$12,2,FALSE)

so take A3 of sheet1 then goto sheet2 vlookup value of A3 there which has 02 as location , then Sheet2!\$A\$3:\$B\$12,2,FALSE)

Do I interpret/code it correctly as, i'm confused by the simple A3&" 01" ?

Sorry for not being able to post a file, I dont have Excel at home.

Thanks
Ebru

5. ## Re: not able to create a functioning VLOOKUP, MATCH, INDEX formula

so for example if you have location 123456 in A3 and 123456 01 in sheet2 A3 then it will return what is in column B.
Your first post says you have unique numbers in col A of sheet1 and unique location numbers with 01 in col A of sheet2.
So what I created was a unique number, 123456 in A3 of sheet1 and 123456 01 in A3 of sheet2.
then used that vlookup.
the formula is saying take the value in A3 (of sheet1) and add to it the text space 01 " 01" and find that value in column A of sheet2 and return the value right next to it.
This is simply written based on what you wrote in post 1.
it can be added to in case it errors out for a " 02" like so...
=IFERROR(VLOOKUP(A3&" 01",Sheet2!\$A\$3:\$B\$12,2,FALSE),VLOOKUP(A3&" 02",Sheet2!\$A\$3:\$B\$12,2,FALSE))
But either way, without a sample sheet we're just guessing.

6. ## Re: not able to create a functioning VLOOKUP, MATCH, INDEX formula

Hello

I quickly uploaded a file which explains what I want. The figures in yellow background should be formula generated.

Thank you so much

Ebru

7. ## Re: not able to create a functioning VLOOKUP, MATCH, INDEX formula

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