Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup into 2 shhets
Hi
Used search to find this off biff Sheet1=SSA (lookup table) B2 Sheet2=Oasis Pemb C2:H2 Sheet3=Oasis Minor C2:H2 used this formula and go empty cell. 3 being the 3rd column from+incl.C2. =IF(ISERROR(VLOOKUP(B2,Oasis Pemb!C2:H2,3,0)),IF(ISERROR(VLOOKUP(B2,Oasis Minor!C2:H2,3,0)),"",VLOOKUP(B2,Oasis Minor!C2:H2,3,0)),VLOOKUP(B2,Oasis Pemb! C2:H2,3,0)) What am i doing wrong. Want a n/a if false regards Brian -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200602/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup into 2 shhets
Well, you need apostrophes around the sheet names, i.e. 'Oasis
Pemb'!C2:H2 etc because of the spaces. The range that you are trying to lookup is only one row deep, i.e. from C2 to H2 - presumably this should be something like C2:H20. If you want to return n/a if not found, put this between the "" in the middle of the formula, i.e. "n/a". Try these, and see if you get what you want. Pete |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup into 2 shhets
thanks pete
Nearly there ! When draging formula, box appearing "File not found" and is looking for Oasis Minor. I close box as its only a sheet and not file I am getting result if in Oasis Pemb, but not when number is in Oasis Minor =IF(ISERROR(VLOOKUP(B2,'Oasis Pemb'!$C$2:$H$1000,3,0)),IF(ISERROR(VLOOKUP(B2, 'Oasis Minor'!$C$2:H1000,3,0)),"n/a",VLOOKUP(B2,'Oasis Minor'!C2:H1000,3,0)), VLOOKUP(B2,'Oasis Pemb'!$C$2:$H$1000,3,0)) Any idea's? Pete wrote: Well, you need apostrophes around the sheet names, i.e. 'Oasis Pemb'!C2:H2 etc because of the spaces. The range that you are trying to lookup is only one row deep, i.e. from C2 to H2 - presumably this should be something like C2:H20. If you want to return n/a if not found, put this between the "" in the middle of the formula, i.e. "n/a". Try these, and see if you get what you want. Pete -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup into 2 shhets
There is an implied priority in your formula - it will try to find a
match in the Oasis Pemb sheet first,and if there is a match it will eturn values from there (ignoring the Oasis Minor sheet). If it doesn't find a match in Oasis Pemb then it will look in Oasis Minor. So, in order to get it to retrieve values from Oasis Minor, you must ensure that there is no match in the other sheet. By the way, you have a mixture of relative and absolute addresses for the ranges - I would suggest making them all absolute to avoid any confusion. Hope this helps. Pete |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup into 2 shhets
Hi Pete
I got this formula from the search engine as I have no VBA experiance, I usually record the macro and edit occastionally with simple stuff. No Knowledge of r"elative or absolute address," nor the whole formula for that matter. No data will be repeated in the two sheets. To be a pain, can you fix it? regards Pete wrote: There is an implied priority in your formula - it will try to find a match in the Oasis Pemb sheet first,and if there is a match it will eturn values from there (ignoring the Oasis Minor sheet). If it doesn't find a match in Oasis Pemb then it will look in Oasis Minor. So, in order to get it to retrieve values from Oasis Minor, you must ensure that there is no match in the other sheet. By the way, you have a mixture of relative and absolute addresses for the ranges - I would suggest making them all absolute to avoid any confusion. Hope this helps. Pete -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200602/1 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup into 2 shhets
Hi Brian,
the formula looks fine to me - you can change to absolute addresses by using $ as follows: =IF(ISERROR(VLOOKUP(B2,'Oasis Pemb'!$C$2:$H$1000,3,0)), IF(ISERROR(VLOOKUP(B2,'Oasis Minor'!$C$2:$H$1000,3,0)),"n/a", VLOOKUP(B2,'Oasis Minor'!$C$2:$H$1000,3,0)), VLOOKUP(B2,'Oasis Pemb'!$C$2:$H$1000,3,0)) Your earlier comment about File not found implies that your sheet names are not spelt exactly the same as used within the formula, and as you are not getting returns from the Oasis Minor sheet I suggest you look at that tab - do you have two spaces in the middle, or one or more spaces at the end of the name? Hope this helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |