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.
Re: Vlookup needs revision
Alright so the proper file is here http://db.tt/9QPe8pEi I can assume the reason I'm getting the error is because there are multiple of some on the numbers how do I pull only one seeing ad they are all the same ?
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
I have never used either of those functions i was hoping there was a way to make this work with just a vlookup...
Re: Vlookup needs revision
Quote from Zpichette;678449I 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"]
[/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
I got problem with download so here is a formula/:
=IF(ISNA(INDEX(Sheet2!$A$2:$AA$46312,MATCH($C6,Sheet2!$F$2:$F$46312,0),MATCH(H$5,Sheet2!$A$1:$AA$1,0))),"",INDEX(Sheet2!$A$2:$AA$46312,MATCH($C6,Sheet2!$F$2:$F$46312,0),MATCH(H$5,Sheet2!$A$1:$AA$1,0)))
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....
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
Your a life saver!!!!!!!!!!!!!!
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"]
CDN=>USD
[/td]
[TD="align: right"]0.97[/TD]
Euro=>USD
[/td]
[TD="align: right"]1.3194[/TD]
MXN=>USD
[/td]
[TD="align: right"]0.0790[/TD]
JPY =>USD
[/td]
[TD="align: right"]0.01[/TD]
[/TABLE]
Re: Vlookup needs revision
sorry... missed the page, this was already corrected.
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.
Don’t have an account yet? Register yourself now and be a part of our community!
