Hi!

If you only have 2 sheets to lookup try this:

Maybe name each sheets range something like:

PM1_rng refers to PM1!$A$2:$G$20
PM2_rng refers to PM2!$A$2:$G$20

=IF(COUNTIF(INDEX(PM1_rng,,1),A1),VLOOKUP(A1,PM1_r ng,2,0),VLOOKUP(A1,PM2_rng,2,0))

Biff

"John" wrote in message
...
I have three worksheets: Main, PM1, PM2
I have a named range "PM_Ranges=PM1:PM2!$A$2:$G$20 "

Each worksheet has the same amount of columns. Column A in worksheets PM1
and PM2 has a unique reference "Project" for each of the worksheets.
Worksheet Main column A has all the "Projects".

I am attempting to use Main as a combination of all worksheets by
attempting
to search the PM_Range and get all the corresponding column information
for a
given project.

I have attempted the following:
= vlookup(Main!A1,PM_Range,2,false) and I only get #Value.

but if I use
= vlookup(Main!A1, PM1!$A$2:$G$20,2,false)
it works but only for the PM1 worksheet

I need it to search all worksheets.

Any thoughts?