Searching multiple Worksheets

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I hope someone can help. I have a workbook with a few worksheet. Each worksheet represents an item, and each item is serialized, so each worksheet is a list of all serialized items.
For example worksheet named Radios. In column A is a list of all serial numbers for Radios. Column B is Name
In another workbook, employees have a worksheet with their name on it. In this worksheet is a list containing three items with serial numbers. Their name is also listed in a Cell of this workbook.
What I am trying to do is create a formula that will take a serial number in column A in workbook 1, check all the worksheets in Workbook 2, for that serial number, and if it is found in workbook 2, return the name of the worksheet the serial number is found, to workbook 1, Column B. Here is the formula I put into Column B2

=IFERROR(INDEX('[Staff Inventory.xlsm]FIRSTNAME, LASTNAME:FIRSTNAME, LASTNAME'!$B$3:$E$3,MATCH(A2,'[Staff Inventory]FIRSTNAME, LASTNAME:FIRSTNAME, LASTNAME'!$E$11:$F$11,0),1),"no match")

So, Staff Inventory is the worksheet with the names of staff as work sheets. $B$3:$E$3 is the location of the employees name. $E$11:$F$11 is the location of the serial number

So in my mind the formula in Workbook 1, Column B2 says: look at this cell $E$11:$F$11in all worksheets in this workbook Staff Inventory.xlsm. If the serial number matches cell A2, return the contents of $B$3:$E$3. If a match is not found in any of the worksheets, return "no match"

It will not work with a range of worksheets, but will work if I reference a single worksheet. Can someone help? Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
oops. Staff Inventory is the workbook, not the worksheet.
 

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
Hello all,

I hope someone can help. I have a workbook with a few worksheet. Each worksheet represents an item, and each item is serialized, so each worksheet is a list of all serialized items.
For example worksheet named Radios. In column A is a list of all serial numbers for Radios. Column B is Name
In another workbook, employees have a worksheet with their name on it. In this worksheet is a list containing three items with serial numbers. Their name is also listed in a Cell of this workbook.
What I am trying to do is create a formula that will take a serial number in column A in workbook 1, check all the worksheets in Workbook 2, for that serial number, and if it is found in workbook 2, return the name of the worksheet the serial number is found, to workbook 1, Column B. Here is the formula I put into Column B2

=IFERROR(INDEX('[Staff Inventory.xlsm]FIRSTNAME, LASTNAME:FIRSTNAME, LASTNAME'!$B$3:$E$3,MATCH(A2,'[Staff Inventory]FIRSTNAME, LASTNAME:FIRSTNAME, LASTNAME'!$E$11:$F$11,0),1),"no match")

So, Staff Inventory is the workbook with the names of staff as work sheets. $B$3:$E$3 is the location of the employees name. $E$11:$F$11 is the location of the serial number

So in my mind the formula in Workbook 1, Column B2 says: look at this cell $E$11:$F$11in all worksheets in this workbook Staff Inventory.xlsm. If the serial number matches cell A2, return the contents of $B$3:$E$3. If a match is not found in any of the worksheets, return "no match"

It will not work with a range of worksheets, but will work if I reference a single worksheet. Can someone help? Thanks
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
You've got too many arguments (ranges) in several parts of your formula. Please post the 'actual' formula that works with a single sheet so that we have something useful to work from.

edit:- please do this in your original thread, Searching multiple Worksheets

I'm flagging this to admin as a duplicate thread to be locked.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,116
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I have merged your two threads together.

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

If you do not receive a response, you can "bump" it by replying to it again, though we advise you to wait 24 hours before doing and not to bump a thread more than once a day.
 

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows
While I can't post the original formula, do to privacy issues, the formula is virtually identical to the formula posted, minus the name range for the array, like this
=IFERROR(INDEX('[Staff Inventory.xlsm]FIRSTNAME, LASTNAME'!$B$3:$E$3,MATCH(A2,'[Staff Inventory]FIRSTNAME, LASTNAME'!$E$11:$F$11,0),1),"no match")

As far as reposting, I wanted to change the name of the post because I believed it was more of an INDEX and MATCH issue, rather than a multiple worksheet search issue.
I could not find how to rename a post, so I reposted it with a new name.
My apologies
 

Flagz

New Member
Joined
Apr 23, 2020
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

anyone?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,116
Office Version
  1. 365
Platform
  1. Windows
Personally, I would never use Excel to do this, because what you are describing is more of a relational database, and I would Access to do it.
But there are ways to do it in Excel. Have a look at this link here, which shows you different methods you may be able to use: Easy way to Lookup values across multiple worksheets in Excel
 

Forum statistics

Threads
1,148,020
Messages
5,744,359
Members
423,863
Latest member
teehexcel

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
Top