Index and match multiple sheets

Hi, I have an index and match formula set up for 1 sheet and it works great but how do I handle it if I want the index to look at data from multiple sheets to pull the correct information. My current formula is:

=INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0))

I would like the index to look at the site number on about 3 or more different sheets. Can this be done?

Find more posts tagged with

Formulas

Comments

L_123

Is the site number unique? Can it appear on multiple sheets?

if it is unique I would use stacked iferrors

=iferror(iferror(INDEX({Site Number}, MATCH([Site ID]62, {Site Name}, 0)),INDEX({Site Number ref 2}, MATCH([Site ID ref2]62, {Site Name ref 2}, 0)),INDEX({Site Number ref 3}, MATCH([Site ID ref 3]62, {Site Name ref3}, 0))

Paul Newcome

Since we are matching on the Site ID, wouldn't THAT have to be the unique value for this formula to work, or am I reading things wrong?

L_123

yeah you're right. brain fart on my part. [Site ID] should be constant for all three instead of having the ref 2 and ref 3 that I added.

Paul Newcome

I would leave the ref 2 and ref 3. Since we are looking at three different sheets, if the Site ID is not found on sheet 1, it will throw a #NO MATCH error which will trigger the IFERROR and move on to sheet 2, and then to sheet 3 if needed.

Just wanted to make sure we were establishing the correct unique values to be referenced so that no one goes through the effort of changing things up unnecessarily.

The way this week has been for me already, I figured I had read it wrong. Haha.

Sign in to join the conversation!

Quick Links

Trending discussions

Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Countifs result not matching filter
In my sheet, I have a filter for 2 values (see below images). The result is 294 In my report this formula yields 304. =COUNTIFS({helper-child}, "1", {gapStatus}, <>"Rejected (not a GAP)", {gapStatus}, <>"removed - duplicate", {gapStatus}, <>"removed - not valid") Why are they not matching? What am I missing?
Using CONTAINS in a MAX(COLLECT) function
I have a schedule that has a task name column, a date column and a task type column. I am trying to build a formula (in another sheet) that will return the latest date based on when the task type is "APP" and the task name contains "GS" somewhere in the cell. Here is the formula I have come up with: MAX(COLLECT({Schedule…