Index and match multiple sheets

shornbuckle

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?

 

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.