Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create range bar graph | Charts and Charting in Excel | |||
How to Select a relative range with Using "Find" and Offset() | Excel Discussion (Misc queries) | |||
Reveal cell formats and extendable range in tool/statusbar/icon. | Excel Worksheet Functions | |||
Vlookup - Range | Excel Discussion (Misc queries) | |||
Dynamic Print Range Help | Excel Worksheet Functions |