Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup in Multiple Workbooks
Hello
Can anyone help please? I need to look up an estimate number which is workbook number 1 and find it in work book number 2 and then find the selling price value and enter it in a blank cell in work book number 1. I thought this would work with a vlookup but I only get some correct results and then the ones it can not locate it puts in the last value that is found in workbook 2. How do I get the spreadsheet to give me only the correct answers and where the estimate number can not be found leave it blank or but n/a? The estimate numbers are combinations of letters and numbers e.g. I45236. Could this be causing the problem? -- Thanks for your advice in advance. Excel Crazy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup in Multiple Workbooks
Use False as the fourth parameter of the VLOOKUP function to force an exact match. To prevent the
NA from appearing, use =IF(ISERROR(VLOOKUP(....)),"",VLOOKUP(....)) HTH, Bernie MS Excel MVP "Excelcrazy" wrote in message ... Hello Can anyone help please? I need to look up an estimate number which is workbook number 1 and find it in work book number 2 and then find the selling price value and enter it in a blank cell in work book number 1. I thought this would work with a vlookup but I only get some correct results and then the ones it can not locate it puts in the last value that is found in workbook 2. How do I get the spreadsheet to give me only the correct answers and where the estimate number can not be found leave it blank or but n/a? The estimate numbers are combinations of letters and numbers e.g. I45236. Could this be causing the problem? -- Thanks for your advice in advance. Excel Crazy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup in Multiple Workbooks
Thanks
I tried this but it did not work. With false added as the fourth parameter it brought back no answer for any row. With the fourth parameter left out I get the correct answer where an exact match of the estimate number can be located, but where the estimate number could not be found it puts in a figure from the closest estimate number that it can find. I need the ones that can not be located to be left either as blank cells or state n/a . Where am I going wrong? -- Thanks for your advice in advance. Excel Crazy "Bernie Deitrick" wrote: Use False as the fourth parameter of the VLOOKUP function to force an exact match. To prevent the NA from appearing, use =IF(ISERROR(VLOOKUP(....)),"",VLOOKUP(....)) HTH, Bernie MS Excel MVP "Excelcrazy" wrote in message ... Hello Can anyone help please? I need to look up an estimate number which is workbook number 1 and find it in work book number 2 and then find the selling price value and enter it in a blank cell in work book number 1. I thought this would work with a vlookup but I only get some correct results and then the ones it can not locate it puts in the last value that is found in workbook 2. How do I get the spreadsheet to give me only the correct answers and where the estimate number can not be found leave it blank or but n/a? The estimate numbers are combinations of letters and numbers e.g. I45236. Could this be causing the problem? -- Thanks for your advice in advance. Excel Crazy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup in Multiple Workbooks
You could be going wrong due to formatting - Excel may show whole numbers when they are entered with
one decimal, and you may think are that you have an exact match but you don't really. Post an example of your data table, and the formula that you are using, and we'll figure it out.... HTH, Bernie MS Excel MVP "Excelcrazy" wrote in message ... Thanks I tried this but it did not work. With false added as the fourth parameter it brought back no answer for any row. With the fourth parameter left out I get the correct answer where an exact match of the estimate number can be located, but where the estimate number could not be found it puts in a figure from the closest estimate number that it can find. I need the ones that can not be located to be left either as blank cells or state n/a . Where am I going wrong? -- Thanks for your advice in advance. Excel Crazy "Bernie Deitrick" wrote: Use False as the fourth parameter of the VLOOKUP function to force an exact match. To prevent the NA from appearing, use =IF(ISERROR(VLOOKUP(....)),"",VLOOKUP(....)) HTH, Bernie MS Excel MVP "Excelcrazy" wrote in message ... Hello Can anyone help please? I need to look up an estimate number which is workbook number 1 and find it in work book number 2 and then find the selling price value and enter it in a blank cell in work book number 1. I thought this would work with a vlookup but I only get some correct results and then the ones it can not locate it puts in the last value that is found in workbook 2. How do I get the spreadsheet to give me only the correct answers and where the estimate number can not be found leave it blank or but n/a? The estimate numbers are combinations of letters and numbers e.g. I45236. Could this be causing the problem? -- Thanks for your advice in advance. Excel Crazy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple if or multiple vlookup | Excel Worksheet Functions | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
how do I use vlookup for multiple occurrences of the same value | Excel Worksheet Functions | |||
Macro that will add data from multiple workbooks to the 1st open r | Excel Discussion (Misc queries) | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |