How to use Index/Match instead of vlookup still using sheet references

edited 12/09/19 in Formulas and Functions


We have started to build a large master sheet using vlookups (mostly countifs and sumifs) cross referencing our other sheets but have quickly run into the 25000 limit.

It has been suggested that by using index/match instead we can avoid this, can anyone help how this works?

some example of formula we are currently using: - how would we change this to index/match

=COUNTIFS(OR({Sheet 1 Completed Hour}, @cell&lt;7, @cell >18), OR({Sheet 1 Created Hour}, @cell&lt;7, @cell >18), {Sheet 1 New Job}, <> “Direct Entry", {Sheet 1 Current Month}, 1, {Sheet 1 New Job}, <>"KIOSK", {Sheet 1 Job Time}, <60, {Sheet 1 Name}, Name1)





  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are correct that INDEX/MATCH can be used in place of VLOOKUP, but in this case you are not using a VLOOKUP.


    To help cut down the number of cross sheet references, you can use some helper columns on the original sheet to check if certain criteria is met. It seems like the most variable part of your formula would be the name, so if you use an IF statement in a checkbox column on the original sheet to flag if all of the other criteria are met, you could then use a formula on your metrics sheet along the lines of


    =COUNTIFS({Sheet 1 Helper Column}, 1, {Sheet 1 Name}, Name1)


    This takes you from 6 different cross sheet references to 2 which means you are referencing only a third of the cells you were before.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!