Vlookup in multiple sheets

[Vish]

I have 4 sheets of data(sheet 1,sheet2,...) and 10 columns of data on each
sheet, each sheet starts with column A as the part number and 10 as a price.
I have a separate sheet called summary, that has a list of part numbers in
column A, i want to match this list to return the values that are in the 4
sheets of data.

 

[Jacob Skaria]

Hi Vish

If the 4 sheets are named as Sheet1, Sheet2, Sheet3 and Sheet4 try the below
formula from the summary sheet cell B1 with a valid part number in cell A1

In Summary sheet cell B1.
=VLOOKUP($C$1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:4"))&"!A:A"),$C$1)>0,0)&"!A:J"),COLUMN(),0)

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

Once applied copy/drag the fomula to the right (upto Col J)..

 

[Ashish Mathur]

Hi,

You may refer to question 9 at the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com