+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    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. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,136

    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.
    Adjust per your actual data.
    for a better answer you probably ought to post a sample sheet.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,807

    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”.
    • select your file(s)
    • click “Upload”
    • click “Close window”
    • click “Submit Reply”

    The file name will appear at the bottom of your reply.
    Dave

  4. #4
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    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. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    2012 for Mac at home
    Posts
    7,136

    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. #6
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

    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. #7
    Registered User
    Join Date
    04-28-2016
    Location
    Paris
    MS-Off Ver
    2010
    Posts
    20

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create an Agenda or similar Using Vlookup or index Match
    By gotid in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-08-2018, 07:41 PM
  2. [SOLVED] VLOOKUP and INDEX/MATCH not functioning (please help)
    By westgigo in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-15-2017, 08:10 PM
  3. Replies: 0
    Last Post: 09-08-2017, 09:31 AM
  4. Create own formula to replace VLOOKUP or INDEX & MATCH
    By KriswithaK in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-01-2017, 05:16 AM
  5. How to create a countif index match formula
    By MZing81 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-22-2016, 04:38 PM
  6. Trying to create an if index match formula of some sort
    By Sivalensis in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-03-2014, 11:27 AM
  7. Replies: 4
    Last Post: 01-13-2014, 04:16 AM

Tags for this Thread

Bookmarks

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