How to make a relative reference in an array formula in Google Sheets

Question

Here's the straightforward version of my question:

I want to change the following formula to an array formula...

Original formula (from cell J2):

=if(F4="VM:",G4,J1)

My attempt at converting to an array formula (in cell K1):

=arrayformula(if(row(A:A)=1,G3,if(F:F = "VM:",G:G,indirect("K"&row(A:A)-1))))

This works on rows where F = "VM:", but returns a #REF error on other rows. Function INDIRECT parameter 1 value is 'K0'. It is not a valid cell/range reference.

Thoughts on how to fix this?

The more complex version of my question. i.e. Why am I trying to do this?...

I have a weird spreadsheet with data that should really be in a Wiki.

I want to create filter views for each person so they can easily filter on only their own vendors. The original formula will work, but as more vendors are added, I'd like for the formula to automatically work for those rows as well.

If there's a better way to do this, I'm listening.

You have to change sharing setting for your sample - it yells: "Access Denied" — Grzegorz Mogilewski
Shoot. It's only giving me the option to share within my company. I've attempted to add a screen shot. That may help. — sandeaj
It would be easier to have a screenshot that includes columns and rows headers... :) — Grzegorz Mogilewski

Grzegorz Mogilewski Grzegorz Mogilewski · Accepted Answer · 2018-09-05T21:12:45

I don't exactly understand your needs, but If you want to autopopulate your formula, then you only need this code in desire column in row 4 (you can change this to any other - this will autofill down from this point):

=ArrayFormula(if(F4:F="VM:",G4:G,J1:J))

Is this what you are trying to get?

After clarification:

You need this code in J2 only:

=ArrayFormula(VLOOKUP(ROW(J2:J),
  QUERY({F:G,ROW(G:G)},"select Col3,Col2 where Col1='VM:'",1)
,2,1)
)

Works for you?

How to make a relative reference in an array formula in Google Sheets

2 Answers

After clarification: