Vlookup needs revision

  • For the life of my i can not figure out why this vlookup isnt working can someone please explain to me and fix it?


    In column H of sheet 1 you should be able to figure out what im doing by looking at the formula if not i can explain just ask.


    http://db.tt/9Oxfxaet - Not correct information


    [Edit didnt have all the information ill update with correct file soon]

  • Re: Vlookup needs revision


    Zpichette


    What is your vlookup meant to return?


    The header says PN+PLANT I guess the PN is Part Number...
    Change the 1 to 3, this will return the Part Number.


    The PLANT I assume is the Plant (SU Code) this cannot be returned as it is before the lookup value. (lookup value in column F what you want returned is in column C)


    Also most of the numbers in sheet1 do not appear in sheet 2.


    Windy

  • Re: Vlookup needs revision


    Your formula in H6 doesn't seem to be copied right down. If I copy it down, some data (where matches are found) is returned.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Vlookup needs revision


    Your lookup range starts in column A so this is where the VLOOKUP will search for a match, that is why you have the errors.


    If you change the range to start in column F then this will return something.


    This will still give you 1499 errors.


    You might want to look into Index and Match functions.


    Windy

  • Re: Vlookup needs revision


    Quote from Zpichette;678449

    I have never used either of those functions i was hoping there was a way to make this work with just a vlookup...


    H6 drag down and accross
    =IFERROR(INDEX(Sheet2!$A$2:$AA$46312,MATCH($C6,Sheet2!$F$2:$F$46312,0),MATCH(H$5,Sheet2!$A$1:$AA$1,0)),"no match")
    Copy from Sheet2 any of the header you want to rertive.
    To example Copy/Paste from Sheet2 "Part Description" into H5 Sheet1.
    [TABLE="width: 237"]

    [tr]


    [td][/td]


    [/tr]


    [/TABLE]

  • Re: Vlookup needs revision


    I dont think this works is excel 2003 or i am doing something wrong... can you make that column H work and send me the file becuase my tiny brain doesnt work very well.

  • Re: Vlookup needs revision


    Alright so ive made some modifications to the sheet to make everything more clear, Here is the link to the updated version of this file. I want to get rid of the NA's there is aprroximately 47% of the cells in column J returning NA which shouldnt be the case because if you manually check the part numbers and the prices match up so there should be no reason for this NA please tell me what the heck is going on here....


    http://db.tt/Q2Kxcpd9

  • Re: Vlookup needs revision


    OK, here are the formulas that should work:


    In J6 put =VLOOKUP(I6,Budget!$C$2:$EO$7493,44,1) and copy down
    In K6 put =VLOOKUP(C6,Budget!$C$2:$G$7494,5,1) and copy down
    in M6 put =VLOOKUP(C6,Budget!$C$2:$Z$7494,24,1) and copy down


    The problem with column J is that you were looking at column A on the Budget sheet rather than column C, so I changed this and shifted the lookup up column two columns back.


    Also in all three formulas, you used relative references rather than absolute references. That is what the $ does in the cell range. Hope this helps some.

  • Re: Vlookup needs revision


    Alright next step.... Final inqury about this sheet.


    So some of the currencies of the two different files are conflicting what kind of formula would i use (in column P) to fix this i.e. make it so they are all usd.


    The exchange rates i must use are
    [TABLE="width: 141"]

    [tr]


    [td]

    CDN=>USD

    [/td]


    [TD="align: right"]0.97[/TD]

    [/tr]


    [tr]


    [td]

    Euro=>USD

    [/td]


    [TD="align: right"]1.3194[/TD]

    [/tr]


    [tr]


    [td]

    MXN=>USD

    [/td]


    [TD="align: right"]0.0790[/TD]

    [/tr]


    [tr]


    [td]

    JPY =>USD

    [/td]


    [TD="align: right"]0.01[/TD]

    [/tr]


    [/TABLE]

  • Re: Vlookup needs revision


    =IF(K6="usd",J6,IF(K6="eur",J6*1.3194,IF(K6="mxn",J6*0.079,IF(K6="jpn",J6*0.01,0)))) That returns j6 modified by the rates you currently listed. You could also set up in your file 3 spots for eur, jpn, and mxn and reference those instead of the values in this formula, which would let you change the rates much easier in my opinion. Hope this helps.

  • Re: Vlookup needs revision


    Also, may want to change the format on cells j:n since it drops all the way down to 43k. That may speed up your file if you've noticed it running slowly at all.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!