# Searching multiple Worksheets

#### Flagz

##### New Member
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
oops. Staff Inventory is the workbook, not the worksheet.

#### Flagz

##### New Member
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
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

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

anyone?

#### Flagz

##### New Member
What am I doing wrong?

Bump up

#### Joe4

##### MrExcel MVP, Junior Admin
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

Replies
2
Views
485
Replies
0
Views
258
Replies
2
Views
377
Replies
7
Views
597
Replies
1
Views
201

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?

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