VLOOKUP in 4 work sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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:
 
MESTRELLA29 said:
Can you explain the
"Entered as an array using the key combination of CTRL,SHIFT,ENTER:
" thing, I understand the firts part except this.
Click to expand...
....

Look up "array formula" in Excel's help.
 
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?
 
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?
 
Back
Top