INDEX/MATCH function across more than 2 sheets

pageella

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))

parulmishra

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