vlookup over multiple sheets

pete4monc

Board Regular
Joined
Jan 12, 2014
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Hi all
I have two workbooks and need workbook 1 to look into workbook 2 and return contents in 8th column when A2 is matched,
But Workbook 2 has multiple sheets.

I have done the formula but it only searches the one named sheet and works fine.

My formula
=VLOOKUP(B2,'F:\BOP\New folder (2)\[Order Review Spreadsheet.xlsx]October 2021'!$A$1:$I$1000,8,0)

Is it possible without a very long formula to search all sheets in another workbook, fine the match and return to workbook1?

Thanks in advance.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Hi all
I have two workbooks and need workbook 1 to look into workbook 2 and return contents in 8th column when A2 is matched,
But Workbook 2 has multiple sheets.

I have done the formula but it only searches the one named sheet and works fine.

My formula
=VLOOKUP(B2,'F:\BOP\New folder (2)\[Order Review Spreadsheet.xlsx]October 2021'!$A$1:$I$1000,8,0)

Is it possible without a very long formula to search all sheets in another workbook, fine the match and return to workbook1?

Thanks in advance.
Vlookup will return only single answer from multiple sheet.
 
Upvote 0
Vlookup will return only single answer from multiple sheet.
Thanks for your reply Earthworm.
It is working at the moment and returns one answer, but how do I get it to search through multiple sheets for that one answer?
 
Upvote 0
Thanks for your reply Earthworm.
It is working at the moment and returns one answer, but how do I get it to search through multiple sheets for that one answer?
Does answer on each sheet will be different ?
 
Upvote 0
Does answer on each sheet will be different ?
Hi
Sorry if I wasn't clear in my original description.
I need the lookup value in workbook 1, cell B2 to look through all the sheets in workbook 2 (15+ sheets).
Find the value in which ever sheet (will only be one occurrence) and return that value on workbook 1, cell H2.

Many thanks
 
Upvote 0
Hi
Sorry if I wasn't clear in my original description.
I need the lookup value in workbook 1, cell B2 to look through all the sheets in workbook 2 (15+ sheets).
Find the value in which ever sheet (will only be one occurrence) and return that value on workbook 1, cell H2.

Many thanks
wrap the formula using if error

example
iferror(iferror (vlookupsheet1),vlookupsheet2)),vlookupsheet3)))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top