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