Multiple Index and Match formula help.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,040
Office Version
  1. 2019
Platform
  1. Windows
Hi all,

To begin with I have got exactly 27 sheets with me. I need to run index and match formula on my other workbook that searches through all these 27 sheets before it could return me my matched value.

However I am trying to avoid a huge formula that will match all of these 27 sheets/tables.
I have instead put a helper column on the other workbook that will consist of the sheet's name where the index and match should look up the value. hence avoiding all but 1 sheet of these 27 sheets. also this will shorten the formula I suppose.

So I need a formula that can achieve this kind of index and match if possible at all.

So suppose column A comprises the sheet name (alpha) . and column B is the value (peter) to match and column C is where the index and match formula will got to be.

Any help will be appreciated.

Thank u all.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Look at PowerQuery (Get&Transform). Maybe easier will be consolidate all these tables (in background) then do what you want
 
Upvote 0
I'm not sure if that is what I am after. maybe I wasn't thorough in my original post.

=INDEX([Book1]alpha!$B:$B,MATCH(B2,[Book1]alpha!$A:$A,0))

in the above formula the name of the workbook which is Book1 and the name of the sheet which is alpha should be acquired from the cell reference in A2 and C2 respectively. is it possible to do just that?

Look at PowerQuery (Get&Transform). Maybe easier will be consolidate all these tables (in background) then do what you want
 
Upvote 0
To begin with I have got exactly 27 sheets with me. I need to run index and match formula on my other workbook that searches through all these 27 sheets before it could return me my matched value.

based on cite, this is exactly what I said about PowerQuery. (PQ doesn't contain any calssic formula so hard to say what you want to do with your INDEX/MATCH)

read my footnote ;)

if you don't know how PQ works, see here:
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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