Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup accross multiple worksheets
Hi
I am having problems with a vlookup I need to find an estimate number from workbook 1 in workbook 2 and then in an empty cell in workbook 1 bring up the selling price of the estimate that was located in workbook 2. Where an exact match of the estimate number can be located I get the correct result. Where the estimate number can not be found it puts in the selling price from the closest matched estimate. The estimates are a combination of letters and numbers eg I123212A. Could this be come of the problem? If I put in the parameter False I get no results, just a list of N/A. All I want is the correct selling price where it finds an exact match and either a blank cell or N/A where no exact match can be found. What am I doing wrong? -- Thanks for your advice in advance. Excel Crazy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup accross multiple worksheets
Hi
There is no reason why this should not work. Please post the formula you are using. You say, near the beginning of your post, that if an exact match cannot be found to use the nearest. Later on, though, you say to put NA if no exact match is found. Which do you want? Andy. "Excelcrazy" wrote in message ... Hi I am having problems with a vlookup I need to find an estimate number from workbook 1 in workbook 2 and then in an empty cell in workbook 1 bring up the selling price of the estimate that was located in workbook 2. Where an exact match of the estimate number can be located I get the correct result. Where the estimate number can not be found it puts in the selling price from the closest matched estimate. The estimates are a combination of letters and numbers eg I123212A. Could this be come of the problem? If I put in the parameter False I get no results, just a list of N/A. All I want is the correct selling price where it finds an exact match and either a blank cell or N/A where no exact match can be found. What am I doing wrong? -- Thanks for your advice in advance. Excel Crazy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup accross multiple worksheets
Sorry if I have confused you.
I meant that when I use the vlookup formula in the cells where I would expect a N/A, it is currently putting in a figure that appears to be from the closest estimate number that it can find. I want it to leave these cells (ones where no exact match is found) either blank or stating N/A This is the formula: =VLOOKUP(A6,'[Estimates Oct-Dec 05.xls]Sept 05'!$A$3:$Q$690,8) -- Thanks for your advice in advance. Excel Crazy "Andy" wrote: Hi There is no reason why this should not work. Please post the formula you are using. You say, near the beginning of your post, that if an exact match cannot be found to use the nearest. Later on, though, you say to put NA if no exact match is found. Which do you want? Andy. "Excelcrazy" wrote in message ... Hi I am having problems with a vlookup I need to find an estimate number from workbook 1 in workbook 2 and then in an empty cell in workbook 1 bring up the selling price of the estimate that was located in workbook 2. Where an exact match of the estimate number can be located I get the correct result. Where the estimate number can not be found it puts in the selling price from the closest matched estimate. The estimates are a combination of letters and numbers eg I123212A. Could this be come of the problem? If I put in the parameter False I get no results, just a list of N/A. All I want is the correct selling price where it finds an exact match and either a blank cell or N/A where no exact match can be found. What am I doing wrong? -- Thanks for your advice in advance. Excel Crazy |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup accross multiple worksheets
Hi Crazy,
Try this. Itdoes have the 4th argument which you said did not work for whatever reason. First make sure the values are actually identical, no leading or trailing spaces and such. If that doesn't get you there, I would look at a sample of your workbook to see if I fix it. =IF(ISNA(VLOOKUP(A6,'[Estimates Oct-Dec 05.xls]Sept05'!$A$3:$Q$690,8,0)),""(VLOOKUP(A6,'[Estimates Oct-Dec 05.xls]Sept05'!$A$3:$Q$690,8,0)) IF(ISNA(VLOOKUP(...,.....,.,.)),"",VLOOKUP(..,.... .,.,.,)) HTH Regards, Howard "Excelcrazy" wrote in message ... Sorry if I have confused you. I meant that when I use the vlookup formula in the cells where I would expect a N/A, it is currently putting in a figure that appears to be from the closest estimate number that it can find. I want it to leave these cells (ones where no exact match is found) either blank or stating N/A This is the formula: =VLOOKUP(A6,'[Estimates Oct-Dec 05.xls]Sept 05'!$A$3:$Q$690,8) -- Thanks for your advice in advance. Excel Crazy "Andy" wrote: Hi There is no reason why this should not work. Please post the formula you are using. You say, near the beginning of your post, that if an exact match cannot be found to use the nearest. Later on, though, you say to put NA if no exact match is found. Which do you want? Andy. "Excelcrazy" wrote in message ... Hi I am having problems with a vlookup I need to find an estimate number from workbook 1 in workbook 2 and then in an empty cell in workbook 1 bring up the selling price of the estimate that was located in workbook 2. Where an exact match of the estimate number can be located I get the correct result. Where the estimate number can not be found it puts in the selling price from the closest matched estimate. The estimates are a combination of letters and numbers eg I123212A. Could this be come of the problem? If I put in the parameter False I get no results, just a list of N/A. All I want is the correct selling price where it finds an exact match and either a blank cell or N/A where no exact match can be found. What am I doing wrong? -- Thanks for your advice in advance. Excel Crazy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif with multiple worksheets | Excel Worksheet Functions | |||
Printing Multiple Worksheets | Excel Discussion (Misc queries) | |||
Need to have multiple worksheets use a single worksheet | Excel Worksheet Functions | |||
Extracting data from multiple worksheets into a list | Excel Worksheet Functions | |||
vlookup over multiple worksheets | Excel Worksheet Functions |