INDEX/MATCH function across more than 2 sheets

Options
pageella
pageella ✭✭
edited 06/06/23 in Formulas and Functions

Hi,

I have a list of sites and I am try to auto populate the Site type column based on different sheets (Site Index sheet... but each country has one)

I have the formula working to reference just Australia's Site Index Sheet but I need to INDEX all of the countries sheets ...


=INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0))


I tried the below but that doesn't ork:

=INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0)) + INDEX({CEE - Ops Site Index BL}, MATCH([Site Name]@row, {CEE - Ops Site Index Sitename}, 0))

Answers

  • parulmishra
    parulmishra ✭✭✭✭✭
    Options

    You can use IFERROR to do this.. IFERROR(value, value_if_error).

    Something like this

    = IFERROR(INDEX({AU - Ops Site Index BL}, MATCH([Site Name]@row, {AU - Ops Site Index Site name}, 0)),IFERROR(INDEX({CEE - Ops Site Index BL}, MATCH([Site Name]@row, {CEE - Ops Site Index Sitename}, 0)),IFERROR(INDEX.....and so on )

    It will try to search for the SiteIndex in Australia and if it doesn't find it in the sheet then it will through "#NOMatch error, so we will put the Index formula for the next BL sheet in the "value_if_error" place. You can keep on doing it for as many sheets you want

    Parul Mishra

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!