#1




Using IF & VLOOKUP together
I'm posting on here as a last resort, basically I've given up and hope someone can help!
I have 2 spreadsheets, one is a stock list (StockList.xls) and the other is a pick list (PickList.xls) I need the Code_EAN (Column F) in StockList.xls to look at the Barcode (Column D) in PickList.xls IF the Code_EAN is the same as the Barcode then the QTY PICKED (Column G) in PickList.xls is inserted into Amount Picked (Column H) in StockList.xls, this will then amend the Amount Remaining field. If anyone can help with this it would be greatly appreciated Last edited by junction; 11172010 at 05:53 AM. Reason: To add documents 
#2




Try this in Cell H6 (of StockList.xls)
=IF(ISNA(VLOOKUP(F6,[PickList.xls]Pick!$D$2:$G$9,1,FALSE))=TRUE,0,IF(VLOOKUP(F6,[PickList.xls]Pick!$D$2:$G$9,1,FALSE)=F6,VLOOKUP(F6,[PickList.xls]Pick!$D$2:$G$9,4,FALSE),"")) Seems to do the trick though I'm sure there are neater ways! 
#3




Thought my original lookup was a bit top heavy and it later occurred to me that half of it isn't needed. Try this one in H6 instead.
=IF(ISNA(VLOOKUP(F6,[PickList.xls]Pick!$D$2:$G$9,1,FALSE))=TRUE,0,VLOOKUP(F6,[PickList.xls]Pick!$D$2:$G$9,4,FALSE)) 
#4




I happened to notice a tiny typo. Try this
=IF(ISNA(VLOOKUP(F2,'[PickList(1).xls]Pick'!$D$2:$G$9,4,0)),0,VLOOKUP(F2,'[PickList(1).xls]Pick'!$D$2:$G$9,4,0)) 
#5




Hi there Kimberly. No that wasn’t a typo – the first part of the function needs to check to see if the ‘Barcode’ exists in column D of the PickList. I would hope this to be unique within the column.
Surely with your amendment it will check to see if the ‘Qty Picked’ exists in column G of the PickList? That’s most unlikely to be unique. Peter 
#6




Quote:
The barcode doesn't have to be unique and neither of our formulas tests for uniqueness of anything. The =TRUE is redundant. ISNA(blah blah) is either true or not. You do not need to specify =TRUE in the logical_test argument of an IF function. 
#7




Thanks for the reply Kimberly, I can see what you mean. Absolutely no offence taken with the typo bit and far more polite than saying it was my oddball logic!
I only came to this forum to see if I could get help with my issue (none so far) but after browsing a bit I've seen a lot to interest me. Cheers. 
#8




Thank you both for your help.
I've had to ammend the forumla slightly as I had to strip down both the spreadsheets in order to upload them (too big!). This is the formula I now have: =IF(ISNA(VLOOKUP(F6,[Picklist1.xls]Pick!$D$2:$H$493,1,FALSE))=TRUE,0,VLOOKUP(F6,[Picklist1.xls]Pick!$D$2:$H$493,4,FALSE)) Thanks again 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Using Vlookup & IF together  thelauncher  Excel  5  08252013 11:32 PM 
Vlookup Examples Please ?????  AmrMahmoud1982  Excel  2  10102010 06:31 PM 
Help with VLOOKUP  sakhtar  Excel  2  07242010 07:39 PM 
Is this possible using the Vlookup or any other function?  Delson  Excel  4  02082010 01:27 PM 
vlookup question  Tony G  Excel  9  03282009 11:16 PM 