vlookup into 2 shhets

[Brian Thompson via OfficeKB.com]

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

 

[Pete]

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

 

[Brian Thompson via OfficeKB.com]

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]

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

 

[Brian Thompson via OfficeKB.com]

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]

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