Groups

    Excel vlookup help

    Hi all hoping someone can help.

    I need to do a vlookup, column e contains a product code e.g.1234567.
    In sheet 2 i have the relevant product code in Column B what my code is eg.hbis05ss.
    I need for excel to lookup what has been ordered (1234567) and tell me what my code is.

    I hope that makes sense many thanks in advance.

    18 Comments

    Ok so the first product is in sheet 1 in column E and you want the results to show from sheet 2 - where is the matching product code column and which column do you need the corresponding info from?

    Original Poster

    Hi,

    sheet 1 column e is using there order reference
      1732937

    Sheet 2 contains column b and c b denotes there order reference c is what t relates to B.1733273 C.HPS05SS

    Basically i want to have a column inserted that will translate there reference to mine if this makes sense?

    To do a VLOOKUP is the following syntax (in an empty cell):

    =VLOOKUP ( cell_you_want_to_lookup, cell_range_with_corresponding_data, cell_column_number_of_data_to_display, False)

    Set "cell_you_want_to_lookup" as $E2 as this is the product code you want to reference.
    Set "cell_range_with_corresponding_data" as the cells that contain the same product code and the corresponding data you want to display e.g. Sheet2!$A$2:$B$100. Use $ to lock the cells.
    Set "cell_column_number_of_data_to_display" as the column number that contains the data. So for example cell A1 has Product Code and cell B1 hasProduct description, you will enter 2 here, as the second column has the description you want to display that is cross referenced in the first column.

    The last part of the statement is set to false. I cba explaining that.

    Example below:

    =VLOOKUP($E2,Sheet2!$A$2:$B$100,2,False)

    Good luck.

    Banned

    As above but I'd put it as

    =if(isna(vlookup($E2,Sheet2!$A$2:$B$100,2,False)),"item number not found",vlookup($E2,Sheet2!$A$2:$B$100,2,False))



    Edited by: "ODB_69" 10th Apr 2011

    Original Poster

    Thanks for the advise keep getting value not available error?

    Original Poster

    formula used:

    =VLOOKUP(E2,Sheet2!B2:B116,Sheet2!C2:C116,FALSE)

    Banned

    Should have worked so long as you adjusted the values of A2 and B100?

    Original Poster

    ODB_69

    Should have worked so long as you adjusted the values of A2 and B100?



    Nope i have posted the formula i tried?

    Banned

    Oh yh...no wonder it didn't work

    Use my formula but change A to B and B to C

    Original Poster

    ODB_69

    Oh yh...no wonder it didn't workUse my formula but change A to B and B to … Oh yh...no wonder it didn't workUse my formula but change A to B and B to C



    really appreciate your help, i am getting item number not found yet i know the item number is on the list?

    Formula used:

    =IF(ISNA(VLOOKUP($E2,Sheet2!$B$2:$C$100,2,FALSE)),"item number not found",VLOOKUP($E2,Sheet2!$B$2:$C$100,2,FALSE))

    Banned

    Expand range from 100 to whatever it actually is


    If that doesn't work search manually for the number and work out what it is

    Could be format issue? Try text to columns

    Original Poster

    Thanks for all of the help, still cant get it working think i will have to search manually.

    Banned

    You have changed sheet 2 to the correct sheet name haven't you?

    Original Poster

    Cheers ODB yes it is just sheet 2

    Banned

    Personally I would just write =vlookup(

    In the right place and then do it manually...so write that then click on the cell you want it to look at...then highlight area you want it to search so columns b to c of the other sheet. Then stick in 2 and then false (or 0) and you should be done

    follows2006

    formula used:=VLOOKUP(E2,Sheet2!B2:B116,Sheet2!C2:C116,FALSE)



    The bit I highlighted is wrong... It should be :

    =VLOOKUP(E2,Sheet2!B2:C116,2,FALSE)[/quote]

    Edited by: "DragonChris" 10th Apr 2011

    Original Poster

    Its working! Thank you all so much for your help i really appreciate this.

    Np, happy to help.
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Just heard this...2 ★★★★★★★★★★★★★★ congrats to all on 392k ★★★★★★★★★★★★★★7767204
      2. Shana Tova to all Jewish HUKDers1013
      3. word association Game73844474
      4. miikeyblue and shabbird's (but mostly shabbird's) Tuesday night pub quiz!553782

      See more discussions