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

LouSnz · 04/08/19

Hello

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<7, @cell >18), OR({Sheet 1 Created Hour}, @cell<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)

Thanks,

Paul Newcome · 04/09/19

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.

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

Comments

Help Article Resources

Categories