when i change it to "1,0" it doesnt work!

Hmmm...

It works for me.

However, if all you want to do is verify that the name appears on some other
sheet try this instead (normally entered, not array entered although it'll
work either way):

=IF(SUMPRODUCT(COUNTIF(INDIRECT("'"&wslist&"'!A:A" ),A2)),A2,"")

--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
I have now used the formula from your example sheet:

=VLOOKUP(A2,INDIRECT("'"&INDEX(WSList,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSList&"'!A:A"),A2)0,0))&"'!A :B"),2,0)

This works ok, expect the formula returns back information that i dont
really need, i understand this is because of the "2,0" but when i change
it
to "1,0" it doesnt work! i do no want it to return anything additional
back,
for example, as i am just searching for peoples names in the 20 sheets to
see
if they are present i just want it to return the persons name if they are
present, i do not require any additional information being returned.

how can i change the formula to do this?

thanks alot.

"T. Valko" wrote:

Your 2nd formula works but you have mismatched references:

=VLOOKUP(B2,..........,B1)............)

Both of those references need to the same.

Also, don't forget that this is an array formula. Array formulas need to
be
entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)


--
Biff
Microsoft Excel MVP


"Gemz" wrote in message
...
Hi, I have the following 2 formulae and i have been desperately trying
to
make them work but the dont!

I am looking up the value of column D in 1 worksheet to look across 10
different sheets (which are all named) and then return the results as
"1,0"
when done into another sheet. So i am just looking up names from column
D
of
sheet"names" and then looking across 10 sheets, again in the same
column -
D.

These are the formulae i have tried. the 1st works on the sheets being
called sheets 1,2 etc and not being renamed and the 2nd formula allows
you
to
name the sheets then refer to them seperatly. i would like to use the
2nd
as
i all my sheets are named.

=VLOOKUP(B1,INDIRECT("sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("sheet"&ROW(INDIRECT("1:10"))&"!d2:d100"),B 1)0,0)&"!d2:d100"),1,0)



=VLOOKUP(B2,INDIRECT(""&INDEX($A$1:$A$3,MATCH(TRUE ,COUNTIF(INDIRECT(""&$A$1:$A$3&"!d2:d100"),B1)0,0 ))&"!d2:d100"),1,0)


I either get the response N/A or VALUE. i dont know if i have got all
the
referenes correct, maybe thats why its not working.

maybe the "d2:d100" is incorrect as i repeat it in the formula, but
this
is
the range where the lookup should look in.

I hope im making sense.

Thanks for your help.