Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How can I apply a VLOOKUP formula to multiple sheets in Excel 2010?

Our formula is written and it is working on our Sheet 1, but we need to search 3 Sheets (within the same Workbook), preferably simultaneously.

share|improve this question
add comment

1 Answer

Assuming your key is in column A (in all sheets, including the target sheet), and the data to extract is in B columns, type the following formula in column B of the target sheet:

You have to give priorities to the origin sheets. Here I assume that you are about to look in sheet1, then in sheet2 and finally in sheet3. The priority is important because VLOOKUP looks for the first matching key value.

=IFERROR(VLOOKUP(A1,Sheet1!A:B,2,0),IFERROR(VLOOKUP(A1,Sheet2!A:B,2,0),IFERROR(VLOOKUP(A1,Sheet3!A:B,2,0),"*** Missing ***")))

You can change the search area and extracting column number of each origin sheet, but make sure your key is always in the first column (the nearest column to column A or column A itself).

Have fun.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.