Hlookup help

[pm]

I am trying to match the ven# and Vendor name on worksheet1 with the table in
worksheet2(see below). And if they match populate a field in worksheet1 with
the location number. There are same ven# with different vendor name and
locations.
I've created a VLOOKUP to find the location; however it only picks up the
first vendor number...can anyone help please?


Ven # vendor name location #
6163 FORT BEND COUNTY CLERK
6540 GALVESTON COUNTY JP#3 Loc7
6540 GALVESTON COUNTY, JP#1 Lo11
6540 JP5 GALVESTON COUNTY Lo12

 

[Reno]

same ven# and vendor as 6540 is, requires new vendor numbers or making c
olumn 1 the vendor name and the key to your lookup returning the vendor #.
i'd assign new numbers to the vendors.

 

[JMay]

Can you on your sheet2 insert a new column between vendor name and Location #?
If so, then enter in the new cell (first data row - say C2) =A2&B2
Copy down as far as is needed.
You can even HIDE the New Column (with this new formula).

On Your Sheet1 in Cell C2 (first data row) enter:

=VLOOKUP(A2&B2,Sheet2!$C$2:$D$5,2,FALSE)

Copy down as far as is needed.

HTH,
Jim

 

[pm]

Hi Jim,

I believe I am very close in getting the results I need. Great idea....now
getting #N/A

 

[JMay]

Use an If Statement to eliminate any #N/A's by wrapping the statment in the
function ISNA() like:

If(ISNA(VLOOKUP(A2&B2,Sheet2!$C$2:$D$5,2,FALSE)),"",VLOOKUP(A2&B2,Sheet2!$C$2:$D$5,2,FALSE))

HTH

Jim

 

[pm]

=IF(ISNA(VLOOKUP('COST EXPENDED'!B3&'COST
EXPENDED'!C3,Vendors!C$2$320,2,FALSE)),"",VLOOKUP('COST EXPENDED'!B3&'COST
EXPENDED'!C3,Vendors!C$2$320,2,FALSE))

Jim I should have a Location populated in this field, however, it's blank.
Cost Expended = sheet 1....does this look correct? So i am combining vendor
# and Vendor Name from sheet 1 and looking at the table in sheet 2/Vendors
where I've added a new field and combined Ven# and VenName - -when the two
match it should give me the correct location......

 

[JMay]

If the #N/A's represent "missing Locations from the cells" then to use the
formula I last suggested might have been premature. So consider this!! As
you fill in the locations on Sheet2 the Formulaized cell should bring back
the Loc # versus the #N/A...