|
Hlookup help
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 |
Hlookup help
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. "pm" wrote: 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 |
Hlookup help
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" wrote: 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 |
Hlookup help
Hi Jim,
I believe I am very close in getting the results I need. Great idea....now getting #N/A "JMay" wrote: 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" wrote: 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 |
Hlookup help
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" wrote: Hi Jim, I believe I am very close in getting the results I need. Great idea....now getting #N/A "JMay" wrote: 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" wrote: 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 |
Hlookup help
=IF(ISNA(VLOOKUP('COST EXPENDED'!B3&'COST
EXPENDED'!C3,Vendors!C$2:D$320,2,FALSE)),"",VLOOKU P('COST EXPENDED'!B3&'COST EXPENDED'!C3,Vendors!C$2:D$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" wrote: 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" wrote: Hi Jim, I believe I am very close in getting the results I need. Great idea....now getting #N/A "JMay" wrote: 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" wrote: 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 |
Hlookup help
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... "pm" wrote: =IF(ISNA(VLOOKUP('COST EXPENDED'!B3&'COST EXPENDED'!C3,Vendors!C$2:D$320,2,FALSE)),"",VLOOKU P('COST EXPENDED'!B3&'COST EXPENDED'!C3,Vendors!C$2:D$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" wrote: 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" wrote: Hi Jim, I believe I am very close in getting the results I need. Great idea....now getting #N/A "JMay" wrote: 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" wrote: 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 |
| All times are GMT +1. The time now is 11:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com