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

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,COUNTIF(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))),VLOOKUP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sheet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),""))))

Biff

"MESTRELLA29" <MESTRELLA29@discussions.microsoft.com> wrote in message
news:42B43A7C-1975-4592-85E4-0B8AB6EAFFAE@microsoft.com...
> 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
>

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
>

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,COUNTIF(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))),VLOOKUP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sheet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),""))))
>
> Biff
>
> "MESTRELLA29" <MESTRELLA29@discussions.microsoft.com> wrote in message
> news:42B43A7C-1975-4592-85E4-0B8AB6EAFFAE@microsoft.com...
> > 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" <marcoestrella@gmail.com> wrote in message
news:4313DFB7-B943-49B2-9400-552652DF1F72@microsoft.com...
> 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

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,COUNTIF(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))),VLOOKUP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sheet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),""))))
>
> Biff
>
> "MESTRELLA29" <MESTRELLA29@discussions.microsoft.com> wrote in message
> news:42B43A7C-1975-4592-85E4-0B8AB6EAFFAE@microsoft.com...
> > 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
> >
>
>
>

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" <marcoestrella@gmail.com> wrote in message
news:566B35D9-572D-47EE-85E0-E91BC0E3972D@microsoft.com...
> 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,COUNTIF(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))),VLOOKUP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sheet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),""))))
>>
>> Biff
>>
>> "MESTRELLA29" <MESTRELLA29@discussions.microsoft.com> wrote in message
>> news:42B43A7C-1975-4592-85E4-0B8AB6EAFFAE@microsoft.com...
>> > 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
>> >
>>
>>
>>

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" <marcoestrella@gmail.com> wrote in message
> news:566B35D9-572D-47EE-85E0-E91BC0E3972D@microsoft.com...
> > 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,COUNTIF(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))),VLOOKUP(A1,Sheet2!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet3!A:E,2,0))),VLOOKUP(A1,Sheet3!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet4!A:E,2,0))),VLOOKUP(A1,Sheet4!A:E,2,0),IF(NOT(ISERROR(VLOOKUP(A1,Sheet5!A:E,2,0))),VLOOKUP(A1,Sheet5!A:E,2,0),""))))
> >>
> >> Biff
> >>
> >> "MESTRELLA29" <MESTRELLA29@discussions.microsoft.com> wrote in message
> >> news:42B43A7C-1975-4592-85E4-0B8AB6EAFFAE@microsoft.com...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>