ExcelBanter - VLOOKUP in 4 work sheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - VLOOKUP in 4 work sheets (https://www.excelbanter.com/excel-worksheet-functions/94061-vlookup-4-work-sheets.html)

MESTRELLA29

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

Biff

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

Marcelo

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

MESTRELLA29

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

David Biddulph

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

MESTRELLA29

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

Biff

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

MESTRELLA29

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

All times are GMT +1. The time now is 06:54 AM.