VLookup Formula

[pm]

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,"",(VLOOKUP('COST
EXPENDED'!C4,B$2:B$320,3,FALSE)))

Thanks for your help!

 

[akphidelt]

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$320

 

[Sheeloo]

First replace B$2:B$320 in your formula with B$2$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,3,FALSE)),"Not Found",
VLOOKUP('COSTEXPENDED'!C4,Vendors!B,3,FALSE))


This will give you the location if found else will say "Not Found"

In your formula

 

[pm]

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)))

 

[Sheeloo]

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.

 

[Sheeloo]

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,FALSE)),"Not Found",
VLOOKUP(CostExpended!C2,Vendors!B:C,2,FALSE))
TBD =IF(ISNA(VLOOKUP(CostExpended!C3,Vendors!B:C,2,FALSE)),"Not Found",
VLOOKUP(CostExpended!C3,Vendors!B:C,2,FALSE))
XYZ =IF(ISNA(VLOOKUP(CostExpended!C4,Vendors!B:C,2,FALSE)),"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