| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP in 4 work sheets
hi ther everybody, its been a long time.
I am trying to do a Vlookup of a PO# and i what to look in 4 work sheets, Contacts Connectors Inserts Shipped, every work sheet is was the same format, PO# Part Number Item Class Qty Due Date I was usinf the IF funtion but it gets to complicated when I get to the 4th work sheet. Any ideas |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP in 4 work sheets
Hi!
Here's one way: List the sheet names in a range of cells, assume J1:J4: J1 = Contacts (Sheet2 in the 2nd example) J2 = Connectors (Sheet3 in the 2nd example) J3 = Inserts (Sheet4 in the 2nd example) J4 = Shipped (Sheet5 in the 2nd example) Give this list a defined name, say, WSlist. A1 = lookup value Entered as an array using the key combination of CTRL,SHIFT,ENTER: =VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSlist&"'!A1:A10"),A1)0,0))&" '!A1:E10"),2,0) Or, if you want a nested IF type formula: (for 4 sheets) =IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:E,2,0))),VLOOK UP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sh eet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT( ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sh eet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E ,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),"")))) Biff "MESTRELLA29" wrote in message ... hi ther everybody, its been a long time. I am trying to do a Vlookup of a PO# and i what to look in 4 work sheets, Contacts Connectors Inserts Shipped, every work sheet is was the same format, PO# Part Number Item Class Qty Due Date I was usinf the IF funtion but it gets to complicated when I get to the 4th work sheet. Any ideas |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP in 4 work sheets
Hi Mestrella,
did you try to use ISERROR? "MESTRELLA29" escreveu: hi ther everybody, its been a long time. I am trying to do a Vlookup of a PO# and i what to look in 4 work sheets, Contacts Connectors Inserts Shipped, every work sheet is was the same format, PO# Part Number Item Class Qty Due Date I was usinf the IF funtion but it gets to complicated when I get to the 4th work sheet. Any ideas |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP in 4 work sheets
Can you explain the
"Entered as an array using the key combination of CTRL,SHIFT,ENTER: " thing, I understand the firts part except this. "Biff" rote: Hi! Here's one way: List the sheet names in a range of cells, assume J1:J4: J1 = Contacts (Sheet2 in the 2nd example) J2 = Connectors (Sheet3 in the 2nd example) J3 = Inserts (Sheet4 in the 2nd example) J4 = Shipped (Sheet5 in the 2nd example) Give this list a defined name, say, WSlist. A1 = lookup value Entered as an array using the key combination of CTRL,SHIFT,ENTER: =VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSlist&"'!A1:A10"),A1)0,0))&" '!A1:E10"),2,0) Or, if you want a nested IF type formula: (for 4 sheets) =IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:E,2,0))),VLOOK UP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sh eet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT( ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sh eet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E ,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),"")))) Biff "MESTRELLA29" wrote in message ... hi ther everybody, its been a long time. I am trying to do a Vlookup of a PO# and i what to look in 4 work sheets, Contacts Connectors Inserts Shipped, every work sheet is was the same format, PO# Part Number Item Class Qty Due Date I was usinf the IF funtion but it gets to complicated when I get to the 4th work sheet. Any ideas |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP in 4 work sheets
"MESTRELLA29" wrote in message
... Can you explain the "Entered as an array using the key combination of CTRL,SHIFT,ENTER: " thing, I understand the firts part except this. .... Look up "array formula" in Excel's help. -- David Biddulph |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP in 4 work sheets
Thanks it is Working I do not know why, what is
"Entered as an array using the key combination of CTRL,SHIFT,ENTER:" I put in the formula did not worked, did the CTRL,SHIFT,ENTER and it did. how is that? "Biff" wrote: Hi! Here's one way: List the sheet names in a range of cells, assume J1:J4: J1 = Contacts (Sheet2 in the 2nd example) J2 = Connectors (Sheet3 in the 2nd example) J3 = Inserts (Sheet4 in the 2nd example) J4 = Shipped (Sheet5 in the 2nd example) Give this list a defined name, say, WSlist. A1 = lookup value Entered as an array using the key combination of CTRL,SHIFT,ENTER: =VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSlist&"'!A1:A10"),A1)0,0))&" '!A1:E10"),2,0) Or, if you want a nested IF type formula: (for 4 sheets) =IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:E,2,0))),VLOOK UP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sh eet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT( ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sh eet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E ,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),"")))) Biff "MESTRELLA29" wrote in message ... hi ther everybody, its been a long time. I am trying to do a Vlookup of a PO# and i what to look in 4 work sheets, Contacts Connectors Inserts Shipped, every work sheet is was the same format, PO# Part Number Item Class Qty Due Date I was usinf the IF funtion but it gets to complicated when I get to the 4th work sheet. Any ideas |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP in 4 work sheets
It is an array formula, it operates on more than one value at a time.
See this: http://cpearson.com/excel/array.htm Biff "MESTRELLA29" wrote in message ... Thanks it is Working I do not know why, what is "Entered as an array using the key combination of CTRL,SHIFT,ENTER:" I put in the formula did not worked, did the CTRL,SHIFT,ENTER and it did. how is that? "Biff" wrote: Hi! Here's one way: List the sheet names in a range of cells, assume J1:J4: J1 = Contacts (Sheet2 in the 2nd example) J2 = Connectors (Sheet3 in the 2nd example) J3 = Inserts (Sheet4 in the 2nd example) J4 = Shipped (Sheet5 in the 2nd example) Give this list a defined name, say, WSlist. A1 = lookup value Entered as an array using the key combination of CTRL,SHIFT,ENTER: =VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSlist&"'!A1:A10"),A1)0,0))&" '!A1:E10"),2,0) Or, if you want a nested IF type formula: (for 4 sheets) =IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:E,2,0))),VLOOK UP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sh eet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT( ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sh eet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E ,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),"")))) Biff "MESTRELLA29" wrote in message ... hi ther everybody, its been a long time. I am trying to do a Vlookup of a PO# and i what to look in 4 work sheets, Contacts Connectors Inserts Shipped, every work sheet is was the same format, PO# Part Number Item Class Qty Due Date I was usinf the IF funtion but it gets to complicated when I get to the 4th work sheet. Any ideas |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP in 4 work sheets
OK it is working, but it is very slow, I whant to exclude the sheet that has
this formula and link this to another file, can this be done? "Biff" wrote: It is an array formula, it operates on more than one value at a time. See this: http://cpearson.com/excel/array.htm Biff "MESTRELLA29" wrote in message ... Thanks it is Working I do not know why, what is "Entered as an array using the key combination of CTRL,SHIFT,ENTER:" I put in the formula did not worked, did the CTRL,SHIFT,ENTER and it did. how is that? "Biff" wrote: Hi! Here's one way: List the sheet names in a range of cells, assume J1:J4: J1 = Contacts (Sheet2 in the 2nd example) J2 = Connectors (Sheet3 in the 2nd example) J3 = Inserts (Sheet4 in the 2nd example) J4 = Shipped (Sheet5 in the 2nd example) Give this list a defined name, say, WSlist. A1 = lookup value Entered as an array using the key combination of CTRL,SHIFT,ENTER: =VLOOKUP(A1,INDIRECT("'"&INDEX(WSlist,MATCH(TRUE,C OUNTIF(INDIRECT("'"&WSlist&"'!A1:A10"),A1)0,0))&" '!A1:E10"),2,0) Or, if you want a nested IF type formula: (for 4 sheets) =IF(NOT(ISERROR(VLOOKUP(A1,Sheet2!A:E,2,0))),VLOOK UP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sh eet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT( ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sh eet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E ,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),"")))) Biff "MESTRELLA29" wrote in message ... hi ther everybody, its been a long time. I am trying to do a Vlookup of a PO# and i what to look in 4 work sheets, Contacts Connectors Inserts Shipped, every work sheet is was the same format, PO# Part Number Item Class Qty Due Date I was usinf the IF funtion but it gets to complicated when I get to the 4th work sheet. Any ideas |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| deleted work sheets - Excel | Excel Worksheet Functions | |||
| Vlookup? to match column in two sheets | Excel Discussion (Misc queries) | |||
| vlookup in several sheets | Excel Worksheet Functions | |||
| regression lines and r-square in work sheets instead of charts | Charts and Charting in Excel | |||
| Vlookup won't work, any other ideas? | Excel Worksheet Functions | |||
Linear Mode
