| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Part of VLOOKUP not working
Sorry to post like this but I cannot see what is wrong with the following
nested VLOOKUP formula. =IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)) If the contents of B3 are on any sheet except [Accounts.xls]Export_US_Dollars the formula returns the correct data. If B3 is on [Accounts.xls]Export_US_Dollars the formula returns FALSE. The Export_US_Dollars worksheet has just been added, hence the change in formula, it worked fine before. Can anyone point me in the right direction? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Part of VLOOKUP not working
Insert the condition for TRUE at this IF part for "Export_US_Dollars", eg:
,"", ... IF(ISNA(VLOOKUP(...Export_US_Dollars'!$A:$G,2,0)), "", VLOOKUP(...Export_US_Dollars'!$A:$G,2,0)), .. It was missing in your posted formula -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Code Numpty" wrote: Sorry to post like this but I cannot see what is wrong with the following nested VLOOKUP formula. =IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)) If the contents of B3 are on any sheet except [Accounts.xls]Export_US_Dollars the formula returns the correct data. If B3 is on [Accounts.xls]Export_US_Dollars the formula returns FALSE. The Export_US_Dollars worksheet has just been added, hence the change in formula, it worked fine before. Can anyone point me in the right direction? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Part of VLOOKUP not working
After this part:
IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), you need to put in what you want to happen if the data is not found - usually this will be: "", before the next bit, so that you have got something like this: IF(ISNA(vlookup),"",vlookup) Hope this helps. Pete On Dec 10, 1:00*pm, Code Numpty wrote: Sorry to post like this but I cannot see what is wrong with the following nested VLOOKUP formula. =IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)) If the contents of B3 are on any sheet except [Accounts.xls]Export_US_Dollars the formula returns the correct data. If B3 is on [Accounts.xls]Export_US_Dollars the formula returns FALSE. The Export_US_Dollars worksheet has just been added, hence the change in formula, it worked fine before. Can anyone point me in the right direction? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Part of VLOOKUP not working
Thanks Max! I had stared and stared at this and not realised that I'd
overwritten that when I edited the original. Many thanks for taking the time to set me straight. "Max" wrote: Insert the condition for TRUE at this IF part for "Export_US_Dollars", eg: ,"", .. IF(ISNA(VLOOKUP(...Export_US_Dollars'!$A:$G,2,0)), "", VLOOKUP(...Export_US_Dollars'!$A:$G,2,0)), .. It was missing in your posted formula -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Code Numpty" wrote: Sorry to post like this but I cannot see what is wrong with the following nested VLOOKUP formula. =IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)), IF(ISNA(VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_US_Dollars'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Euros'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Export_Sterling'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]Irish_Euros'!$A:$G,2,0)), VLOOKUP($B$3,'C:\Calibration Masters\[Accounts.xls]UK+Irish_Sterling'!$A:$G,2,0)) If the contents of B3 are on any sheet except [Accounts.xls]Export_US_Dollars the formula returns the correct data. If B3 is on [Accounts.xls]Export_US_Dollars the formula returns FALSE. The Export_US_Dollars worksheet has just been added, hence the change in formula, it worked fine before. Can anyone point me in the right direction? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Part of VLOOKUP not working
Welcome, glad that sorted it out for you.
-- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:365 Subscribers:65 xdemechanik --- "Code Numpty" wrote in message ... Thanks Max! I had stared and stared at this and not realised that I'd overwritten that when I edited the original. Many thanks for taking the time to set me straight. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup part of string... | Excel Discussion (Misc queries) | |||
| Vlookup a part of a cell value in another sheet | Excel Worksheet Functions | |||
| 2nd part of formula not working | Excel Worksheet Functions | |||
| Vlookup for part of a word | Excel Worksheet Functions | |||
| Array not working correctly Returns FALSE on second part | Excel Discussion (Misc queries) | |||
Linear Mode
