Vlookup using a range condition
 

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?

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?

I have 50+ worksheets this was just an example

"John" wrote:

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?

Hi!

First you had 2 sheets to lookup, now you have "50+".

How many is "50+"? 52? 77? 128?

<vbg

OK, try this......

The lookup range has to be EXACTLY the same in ALL sheets.

Create a list of all the sheet names. If your sheet names are PMn this will
be easy. Just enter PM1 in the first cell and drag down.

Give this list a name. Something like SheetList.

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRU E,COUNTIF(INDIRECT("'"&SheetList&"'!A2:G20"),A1)0 ,0))&"'!A2:G20"),2,0)

No error checking in this!

Biff

"John" wrote in message
...
I have 50+ worksheets this was just an example

"John" wrote:

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?

Just a slight tweak:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRU E,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A20"),A1)0 ,0))&"'!A2:G20"),2,0)

Changed the range in the COUNTIF function.

Biff

"Biff" wrote in message
...
Hi!

First you had 2 sheets to lookup, now you have "50+".

How many is "50+"? 52? 77? 128?

<vbg

OK, try this......

The lookup range has to be EXACTLY the same in ALL sheets.

Create a list of all the sheet names. If your sheet names are PMn this
will be easy. Just enter PM1 in the first cell and drag down.

Give this list a name. Something like SheetList.

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRU E,COUNTIF(INDIRECT("'"&SheetList&"'!A2:G20"),A1)0 ,0))&"'!A2:G20"),2,0)

No error checking in this!

Biff

"John" wrote in message
...
I have 50+ worksheets this was just an example

"John" wrote:

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?