Index and match multiple sheets

04/15/19 Edited 12/09/19

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_123L_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 NewcomePaul 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_123L_123 ✭✭✭✭✭
    edited 04/16/19

    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 NewcomePaul 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 or Register to comment.