0

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.

Improve this question

1 Answer 1

Reset to default
4

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .