purekarting
New Member
- Joined
- May 22, 2015
- Messages
- 6
I am trying to use MATCH and INDEX to improve performance on a google sheet that has way too many XLOOKUP functions.
I have a helper column (DH) that lists the matching ROW I need from the Visibility sheet.
This formula returns the expected result from column 9 on the Visibility sheet, where the data in DH2 is the row number I need to pull from Visibility:
=IF(DH2="",,INDEX(Visibility!A:O,DH2,9))
I can fill this formula down, and it works fine. But I am curious if I can spill the formula down from the header cell instead of filling it down manually, that way it will also work when new rows are inserted. The below however just spills Visibility Column 9 from top to bottom instead of returning the data from the needed ROW in DH.
=ARRAYFORMULA({"visibility_primary";INDEX({Visibility!A2:O},DH2:DH,1)})
Any ideas?
I have a helper column (DH) that lists the matching ROW I need from the Visibility sheet.
This formula returns the expected result from column 9 on the Visibility sheet, where the data in DH2 is the row number I need to pull from Visibility:
=IF(DH2="",,INDEX(Visibility!A:O,DH2,9))
I can fill this formula down, and it works fine. But I am curious if I can spill the formula down from the header cell instead of filling it down manually, that way it will also work when new rows are inserted. The below however just spills Visibility Column 9 from top to bottom instead of returning the data from the needed ROW in DH.
=ARRAYFORMULA({"visibility_primary";INDEX({Visibility!A2:O},DH2:DH,1)})
Any ideas?
