#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; 11-17-2010 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 |
|
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 |