Index Match for two criteria, then exclude previously returned values

1
votes

Using Google Sheets, I'm trying to figure out how to do an index match so I can find a value based on two crtieria...then as I continue to use the formula it will exclude all previously returned values.

Assuming 3 columns in all examples...

Sheet 1:

a  b  <blank>
a  b  <blank>

I'm trying to return values into the column by looking for both a and b in another sheet...but I want only one new value to be returned each time.

Sheet 2:

a  b  c
a  b  d
a  b  e

So, for sheet one, I'd like the to be:

a  b  c
a  b  d

I'm sure this is possible somehow, I just don't know how to make it happen...

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup

1 Answers

0
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(A1:A&B1:B&COUNTIFS(A1:A&B1:B, A1:A&B1:B, ROW(A1:A), "<="&ROW(A1:A)), 
 {Sheet2!A1:A&Sheet2!B1:B&COUNTIFS(Sheet2!A1:A&Sheet2!B1:B, Sheet2!A1:A&Sheet2!B1:B, 
 ROW(Sheet2!A1:A), "<="&ROW(Sheet2!A1:A)), Sheet2!C1:C}, 2, 0)))

0