Vlookup in multiple sheets

V

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.
 
J

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)..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top