1

I have two lists (variable length) in Excel (1, 2 resp.) and wish to find the union of these:

Union

My research yields numerous solutions / questions to this effect, however, they either focus on Office 365, VB, Lambdas, PowerQuery variations - they do not proivde a function fit for Excel 2010 etc.:

Requirements/research:

  • No Office 365 requirement (so no FilterXML, let, choose, etc. etc. - per here, here, here)
  • Am not interested in VB (trivial) / PowerQuery (ditto - i.e. simple 'insert/data/append 2 or more queries)
  • Am not interested in Lambda variations (per here)
  • Duplicates are fine

Progress

Have been contemplating an offset function that picks up values in the 2nd list once those in the first list are exhausted in an array function -- but am a litte lost otherwise (and don't want to restrict anyone in scope of functions that may be avail. in this regard).

Can someone assist me in finding a function per above pls?

1 Answers1

3

Using INDEX/MATCH:

=IF(ROW(A1)>COUNTA($B$4:$B$6),INDEX($D$4:$D$7,ROW(A1)-COUNTA($B$4:$B$6)),INDEX($B$4:$B$6,ROW(A1)))

Put that in F4 and copy down.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • nice one! thanks (ideally this is in array format - even though I didn't specify this preference - will give it a go and let you know if I don't come right if that's OK).. –  Mar 01 '22 at 23:06