This is the formula that I would like to search on 8 different sheets. The
sheets will all be the same. The range to search is A1:E1000 on each sheet.

=IF(ISERROR(VLOOKUP(A9,'[Okeene raw
materials.xls]Sheet1'!$A$1:$E$1000,4,FALSE)),0,VLOOKUP(A9,'[Okeene raw
materials.xls]Sheet1'!$A$1:$E$1000,4,FALSE))

"Peo Sjoblom" wrote:

Yes, if the sheets are identical to each other.

If you have the lookup value in A2 on a summary sheet and the sheets you
want to lookup are Sheet1:Sheet8,
the table is A1:C200 and you want to return the value in the second column
(B)


=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2"; "Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Shee t8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3"; "Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A 1:A200"),A2)0),0))&"'!A1:C200"),2,0)

entered with ctrl + shift & enter

if you put all sheet names in a range of cells and give it a name it is less
ugly

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A200"),A2)0) ,0))&"'!A1:C200"),2,0)

where MySheets would hold the names

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"dford" wrote in message
...
Can VLOOKUP be used to search columns in multiple sheets in a workbook?