|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
Apr 22nd, 2002, 12:38 PM
|
#1 |
|
Board Regular
Join Date: Apr 2002
Posts: 64
|
I'm trying to use Vlookup to find a part number in an inventory workbook and put the associated cost of that part in another workbook. The problem is that the particular part number that I'm looking for could be located in either of two different worksheets in the inventory workbook. I have each separate vlookup formula working fine, but I don't know how to link them together so that when the first vlookup formula can't find the part number in the first worksheet, it goes to the second.
|
|
|
|
Apr 22nd, 2002, 01:31 PM
|
#2 | |
|
Board Regular
Join Date: Mar 2002
Posts: 363
|
Quote:
=IF(ISERROR(A1),A2,A1) _________________ It's never too late to learn something new. Ricky [ This Message was edited by: Ricky Morris on 2002-04-22 12:32 ] |
|
|
|
|
|
Apr 22nd, 2002, 01:51 PM
|
#3 |
|
New Member
Join Date: Mar 2002
Posts: 34
|
Two Solutions
Solution #1 Since you already have both vlookup formulas working you could hide both columns and place and if statement in view. For example, (hide) (hide) Column A Column B Column C Vlookup 1 Vlookup 2 IF STMT Modify your formulas to the following: Col A =if(isna(vlookup 1,0,vlookup 1) Col B =if(isna(vlookup 2,0,vlookup 2) Col C =if(A1>0,A1,B1) Warning: This will only work if your part numbers are numeric only, and will also cause a problem if the part has any chance of being on both pages. Solution #2 Since vlookup takes the first solution found, you could just sum the two formulas... =vlookup 1 + vlookup 2 Hope this points you in the right direction -Dave |
|
|
|
|
Apr 23rd, 2002, 09:28 AM
|
#4 |
|
Board Regular
Join Date: Apr 2002
Posts: 64
|
I still can't get this to work. The following two formulas work fine independently. When the first formula can't find the part number in the Item worksheet and returns #N/A, I need it to go to the Floor Stock worksheet (second formula) to look for the part number.
=VLOOKUP(B6,'[Wisard Inventory.xls]Item'!$G$4:$BJ$347,56,FALSE) =VLOOKUP(B6,'[Wisard Inventory.xls]Floor Stock'!$E$2:$M$124,9,FALSE) |
|
|
|
|
Apr 23rd, 2002, 09:32 AM
|
#5 |
|
Board Regular
Join Date: Mar 2002
Location: =ActiveCell.Address
Posts: 478
|
Try this - don't know if it'll work but worth a go...
=IF(ISERROR(VLOOKUP(B6,'[Wisard Inventory.xls]Item'!$G$4:$BJ$347,56,FALSE)),VLOOKUP(B6,'[Wisard Inventory.xls]Floor Stock'!$E$2:$M$124,9,FALSE),VLOOKUP(B6,'[Wisard Inventory.xls]Item'!$G$4:$BJ$347,56,FALSE)) |
|
|
|
|
Apr 23rd, 2002, 10:21 AM
|
#6 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Quote:
|
|
|
|
|
|
Apr 23rd, 2002, 01:05 PM
|
#7 |
|
Board Regular
Join Date: Apr 2002
Posts: 64
|
The vlookup formula resides in a spreadsheet named Part List Template which is separate from the Wisard Inventory spreadsheet. The first vlookup works fine until I try to combine it with the second.
|
|
|
|
|
Apr 23rd, 2002, 01:08 PM
|
#8 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Quote:
|
|
|
|
|
|
Apr 24th, 2002, 11:12 AM
|
#9 |
|
Board Regular
Join Date: Apr 2002
Posts: 64
|
Column I in the Part List Template.
|
|
|
|
|
Apr 24th, 2002, 12:14 PM
|
#10 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Quote:
=IF(COUNTIF('[Wisard Inventory.xls]Item'!$G$4:$G$347,B6),VLOOKUP(B6,'[Wisard Inventory.xls]Item'!$G$4:$BJ$347,56,0),VLOOKUP(B6,'[Wisard Inventory.xls]Floor Stock'!$E$2:$M$124,9,0)) should work. You'll get #N/A when B6 is not found in both lookup tables. Note also that I substituted 0 for FALSE because it's same thing for VLOOKUP. Aladin |
|
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
