Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 11-17-2010, 05:30 AM
junction junction is offline Windows Vista Office 2003
Novice
 
Join Date: Nov 2010
Posts: 2
junction is on a distinguished road
Default 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
Attached Files
File Type: xls PickList.xls (27.5 KB, 66 views)
File Type: xls StockList.xls (78.0 KB, 70 views)

Last edited by junction; 11-17-2010 at 05:53 AM. Reason: To add documents
Reply With Quote
  #2  
Old 11-17-2010, 09:32 AM
pdab09 pdab09 is offline Windows XP Office 2007
Novice
 
Join Date: Nov 2010
Posts: 6
pdab09 is on a distinguished road
Default

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!
Reply With Quote
  #3  
Old 11-17-2010, 01:53 PM
pdab09 pdab09 is offline Windows XP Office 2007
Novice
 
Join Date: Nov 2010
Posts: 6
pdab09 is on a distinguished road
Default

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))
Reply With Quote
  #4  
Old 11-17-2010, 05:09 PM
Kimberly Kimberly is offline Windows 7 Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

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))
Reply With Quote
  #5  
Old 11-18-2010, 01:17 AM
pdab09 pdab09 is offline Windows XP Office 2007
Novice
 
Join Date: Nov 2010
Posts: 6
pdab09 is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 11-18-2010, 02:34 AM
Kimberly Kimberly is offline Windows 7 Office 2010 (Version 14.0)
Expert
 
Join Date: May 2010
Posts: 517
Kimberly is on a distinguished road
Default

Quote:
Surely with your amendment it will check to see if the ‘Qty Picked’ exists in column G of the PickList?
No. It does what nesting ISNA inside IF usually does... look to see a if desired formula would return #NA, and if it would, then return something prettier, and if the desired formula would not return #NA, then do the desired formula. I have never seen one where the formula wasn't exactly the same on both sides of the IF. But I did speak out of turn in that while non-conventional and a little bit lengthy, your formula did return the correct result. Sorry about that. I just thought it was a typo... why would someone test to see if a formula would produce an error, and if it would not, do a different formula?

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.
Reply With Quote
  #7  
Old 11-18-2010, 02:47 AM
pdab09 pdab09 is offline Windows XP Office 2007
Novice
 
Join Date: Nov 2010
Posts: 6
pdab09 is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 11-18-2010, 05:15 AM
junction junction is offline Windows Vista Office 2003
Novice
 
Join Date: Nov 2010
Posts: 2
junction is on a distinguished road
Default

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

Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Vlookup & IF together thelauncher Excel 5 08-25-2013 11:32 PM
Vlookup Examples Please ????? AmrMahmoud1982 Excel 2 10-10-2010 06:31 PM
Help with VLOOKUP sakhtar Excel 2 07-24-2010 07:39 PM
Is this possible using the Vlookup or any other function? Delson Excel 4 02-08-2010 01:27 PM
vlookup question Tony G Excel 9 03-28-2009 11:16 PM


All times are GMT -7. The time now is 10:06 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft