Hi I want to check if a value is present across multiple worksheets

So I have a list of integers in the cover sheet
1
2
3
4
5

I then want to look to see if "1" is in column A of worksheet"Main", if it is then display "MAIN" if it is not then check column A of worksheet"Lower", if it is there display "LOWER" if it is nowhere then display "NONE" I had the following formula which works fine except for showing N/A instead of "NONE"

=IF(VLOOKUP(A1,Main!$A$1:$A$5,1,TRUE)=A1,"MAIN",(I F(VLOOKUP(A1,Lower!$A$1:$A$5,1,FALSE)=A1,"LOWER"," NONE")))

however if I replace the numbers above for a string such as names it is very sporadic as to whether it works or not, some will return the correct value and others will return the wrong value, i.e. it is present in one of the worksheets yet still returns an N/A value

Any thoughts as to why it works with an integer but not a string?

Thanks

Richard

Richard Doyle was thinking very hard :
Hi I want to check if a value is present across multiple worksheets

So I have a list of integers in the cover sheet
1
2
3
4
5

I then want to look to see if "1" is in column A of worksheet"Main", if
it is then display "MAIN" if it is not then check column A of
worksheet"Lower", if it is there display "LOWER" if it is nowhere then
display "NONE" I had the following formula which works fine except for
showing N/A instead of "NONE"

=IF(VLOOKUP(A1,Main!$A$1:$A$5,1,TRUE)=A1,"MAIN",(I F(VLOOKUP(A1,Lower!$A$1:$A$5,1,FALSE)=A1,"LOWER"," NONE")))

however if I replace the numbers above for a string such as names it is
very sporadic as to whether it works or not, some will return the
correct value and others will return the wrong value, i.e. it is present
in one of the worksheets yet still returns an N/A value

Any thoughts as to why it works with an integer but not a string?

Thanks

Richard

What you`re getting is VLOOKUP`s standard return when it can`t find a
match. What happens is that your formula never gets to the False part
because the error occurs ahead of that. Look at using the ISERROR()
function inside your formula, perhaps wrapped with OR() function.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Richard,

Am Fri, 16 Nov 2012 16:21:42 +0000 schrieb Richard Doyle:

1
2
3
4
5

I then want to look to see if "1" is in column A of worksheet"Main", if
it is then display "MAIN" if it is not then check column A of
worksheet"Lower", if it is there display "LOWER" if it is nowhere then
display "NONE" I had the following formula which works fine except for
showing N/A instead of "NONE"

you don't have to use VLOOKUP.
Try:
=IF(COUNTIF(Main!$A$1:$A$5,A1)0,"MAIN",IF(COUNTIF (Lower!$A$1:$A$5,A1)0,"LOWER","NONE"))



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2