Sorry, I guess I missed your point. I've never done exactly what you are
attempting, but I think my first choice would be VBA.....and my second would
be to use a helper column with the 20 individual VLOOKUPS there and then use
one master VLOOKUP to check that column for results...........just my
thoughts...

Vaya con Dios,
Chuck, CABGx3




"Gemz" wrote:

But i am looking in a number of sheets, how can i tell this formula to lookup
c:c in each sheet. the thing is because i am only looking up names i just
need to see if the name on one sheet is present anywhere within the 20 sheets
and it doesnt need to return anything other than "1,false" which means just
to return the name IF it appears anywhere within the 20 sheets.

thanks.

"CLR" wrote:

For a VLOOKUP to function with an offset of more than 1, the LookupRange must
include the columns that contain the data to be returned as well as the
column that contains the value to be looked up.........

=VLOOKUP(A1,A:A,1,FALSE) will work...
=VLOOKUPiA1,A:A,2,FALSE) will not....it should be -VLOOKUP(A1,A:B,FALSE)

Vaya con Dios,
Chuck, CABGx3



"Gemz" wrote:

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.