Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Formula
I have a spreadsheet with two sheets - a vendor sheet, listing the vendor
number, name and location, and a cost expended sheet including date, vendor name, dollar amt, etc. I've created a vlookup formula to match the vendor names from both sheets and give me the location in column 3 on the vendor sheet, but I'm not getting the results I expect #N/A. Here's my formula: =IF(VLOOKUP(+'COST EXPENDED'!C4,Vendors!B$2:B$320,3,FALSE)=0,"",(VLOO KUP('COST EXPENDED'!C4,B$2:B$320,3,FALSE))) Thanks for your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Formula
one problem might be is that your look up array is only 1 column and you are
asking for the 3rd column in that array. So B$2:B$320 should be B$2:D$320 "pm" wrote: I have a spreadsheet with two sheets - a vendor sheet, listing the vendor number, name and location, and a cost expended sheet including date, vendor name, dollar amt, etc. I've created a vlookup formula to match the vendor names from both sheets and give me the location in column 3 on the vendor sheet, but I'm not getting the results I expect #N/A. Here's my formula: =IF(VLOOKUP(+'COST EXPENDED'!C4,Vendors!B$2:B$320,3,FALSE)=0,"",(VLOO KUP('COST EXPENDED'!C4,B$2:B$320,3,FALSE))) Thanks for your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Formula
First replace B$2:B$320 in your formula with B$2:D$320 at both places and see
if works. If not then try (Assumption: B is the column to match on and D contains the location, 'COSTEXPENDED'!C4 contains the value to find the location for) =IF(ISNA(VLOOKUP('COSTEXPENDED'!C4,Vendors!B:D,3,F ALSE)),"Not Found", VLOOKUP('COSTEXPENDED'!C4,Vendors!B:D,3,FALSE)) This will give you the location if found else will say "Not Found" In your formula "pm" wrote: I have a spreadsheet with two sheets - a vendor sheet, listing the vendor number, name and location, and a cost expended sheet including date, vendor name, dollar amt, etc. I've created a vlookup formula to match the vendor names from both sheets and give me the location in column 3 on the vendor sheet, but I'm not getting the results I expect #N/A. Here's my formula: =IF(VLOOKUP(+'COST EXPENDED'!C4,Vendors!B$2:B$320,3,FALSE)=0,"",(VLOO KUP('COST EXPENDED'!C4,B$2:B$320,3,FALSE))) Thanks for your help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Formula
I changed my formula as per below results are not found, but vendor names
match: =IF(ISNA(VLOOKUP(+'COST EXPENDED'!C2,Vendors!A$2:C$320,3,FALSE)),"not Found",(VLOOKUP('COST EXPENDED'!C2,A$2:C$320,3,FALSE))) On vendor sheet column A is vendor number, B is vendor Name, and Column C is Vendor Location....... "Sheeloo" wrote: First replace B$2:B$320 in your formula with B$2:D$320 at both places and see if works. If not then try (Assumption: B is the column to match on and D contains the location, 'COSTEXPENDED'!C4 contains the value to find the location for) =IF(ISNA(VLOOKUP('COSTEXPENDED'!C4,Vendors!B:D,3,F ALSE)),"Not Found", VLOOKUP('COSTEXPENDED'!C4,Vendors!B:D,3,FALSE)) This will give you the location if found else will say "Not Found" In your formula "pm" wrote: I have a spreadsheet with two sheets - a vendor sheet, listing the vendor number, name and location, and a cost expended sheet including date, vendor name, dollar amt, etc. I've created a vlookup formula to match the vendor names from both sheets and give me the location in column 3 on the vendor sheet, but I'm not getting the results I expect #N/A. Here's my formula: =IF(VLOOKUP(+'COST EXPENDED'!C4,Vendors!B$2:B$320,3,FALSE)=0,"",(VLOO KUP('COST EXPENDED'!C4,B$2:B$320,3,FALSE))) Thanks for your help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Formula
Are you matching on vendor name or vendor number?
If you are matching on vendor name (as per your first post) then your formula should be; IF(ISNA(VLOOKUP('COST EXPENDED'!C2,Vendors!B$2:C$320,2,FALSE)),"not Found",(VLOOKUP('COST EXPENDED'!B2,A$2:C$320,2,FALSE))) and C2 in Cost Expended should have the Vendor Name. The formula given by me will work if your data looks like; Vendors Sheet A B C <--- columns 1 ABC London <-- data in columns 2 XYZ Chicago COST EXPENDED has ABC or XYZ or other names in Column C If this does not work then pl. post two (corresponding) rows each from the two sheets. "pm" wrote: I changed my formula as per below results are not found, but vendor names match: =IF(ISNA(VLOOKUP(+'COST EXPENDED'!C2,Vendors!A$2:C$320,3,FALSE)),"not Found",(VLOOKUP('COST EXPENDED'!C2,A$2:C$320,3,FALSE))) On vendor sheet column A is vendor number, B is vendor Name, and Column C is Vendor Location....... "Sheeloo" wrote: First replace B$2:B$320 in your formula with B$2:D$320 at both places and see if works. If not then try (Assumption: B is the column to match on and D contains the location, 'COSTEXPENDED'!C4 contains the value to find the location for) =IF(ISNA(VLOOKUP('COSTEXPENDED'!C4,Vendors!B:D,3,F ALSE)),"Not Found", VLOOKUP('COSTEXPENDED'!C4,Vendors!B:D,3,FALSE)) This will give you the location if found else will say "Not Found" In your formula "pm" wrote: I have a spreadsheet with two sheets - a vendor sheet, listing the vendor number, name and location, and a cost expended sheet including date, vendor name, dollar amt, etc. I've created a vlookup formula to match the vendor names from both sheets and give me the location in column 3 on the vendor sheet, but I'm not getting the results I expect #N/A. Here's my formula: =IF(VLOOKUP(+'COST EXPENDED'!C4,Vendors!B$2:B$320,3,FALSE)=0,"",(VLOO KUP('COST EXPENDED'!C4,B$2:B$320,3,FALSE))) Thanks for your help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup Formula
here is my test data;
VENDORS sheet (Columns A, b and C) Vedor Number Vendor Name Location 1 ABC London 2 XYZ Chicago COST EXPENDED SHEET (Col C and D only) Vendor Name Location ABC =IF(ISNA(VLOOKUP(CostExpended!C2,Vendors!B:C,2,FAL SE)),"Not Found", VLOOKUP(CostExpended!C2,Vendors!B:C,2,FALSE)) TBD =IF(ISNA(VLOOKUP(CostExpended!C3,Vendors!B:C,2,FAL SE)),"Not Found", VLOOKUP(CostExpended!C3,Vendors!B:C,2,FALSE)) XYZ =IF(ISNA(VLOOKUP(CostExpended!C4,Vendors!B:C,2,FAL SE)),"Not Found", VLOOKUP(CostExpended!C4,Vendors!B:C,2,FALSE)) Here is the result; Vendor Name Location ABC London TBD Not Found XYZ Chicago Since the formulae are entered in CostExpended sheet there is no need to specify its name before C2 in the formulae "pm" wrote: I changed my formula as per below results are not found, but vendor names match: =IF(ISNA(VLOOKUP(+'COST EXPENDED'!C2,Vendors!A$2:C$320,3,FALSE)),"not Found",(VLOOKUP('COST EXPENDED'!C2,A$2:C$320,3,FALSE))) On vendor sheet column A is vendor number, B is vendor Name, and Column C is Vendor Location....... "Sheeloo" wrote: First replace B$2:B$320 in your formula with B$2:D$320 at both places and see if works. If not then try (Assumption: B is the column to match on and D contains the location, 'COSTEXPENDED'!C4 contains the value to find the location for) =IF(ISNA(VLOOKUP('COSTEXPENDED'!C4,Vendors!B:D,3,F ALSE)),"Not Found", VLOOKUP('COSTEXPENDED'!C4,Vendors!B:D,3,FALSE)) This will give you the location if found else will say "Not Found" In your formula "pm" wrote: I have a spreadsheet with two sheets - a vendor sheet, listing the vendor number, name and location, and a cost expended sheet including date, vendor name, dollar amt, etc. I've created a vlookup formula to match the vendor names from both sheets and give me the location in column 3 on the vendor sheet, but I'm not getting the results I expect #N/A. Here's my formula: =IF(VLOOKUP(+'COST EXPENDED'!C4,Vendors!B$2:B$320,3,FALSE)=0,"",(VLOO KUP('COST EXPENDED'!C4,B$2:B$320,3,FALSE))) Thanks for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative formula to the vlookup formula? | Excel Worksheet Functions | |||
convert vlookup formula to link formula | Excel Worksheet Functions | |||
Vlookup formula | Excel Discussion (Misc queries) | |||
Excel 2002 VLOOKUP formula or other formula | Excel Discussion (Misc queries) | |||
Vlookup formula | Excel Worksheet Functions |