CODEWITHSUNDEEP
arraysgoogle-sheetsformula
adamsportstech
adamsportstech

Reputation: 347

Use array formula with index match

Is it possible to do an array formula with index match:

e.g: 

=arrayformula(if(len(A3:A),INDEX('SheetB'!E:E,MATCH(A3:A,'SheetB'!H:H,0))))

If not, is there a solution that doesn't involve google scripts?

Upvotes: 8

Views: 58329

Answers (6)

John McCombs
John McCombs

Reputation: 636

If you look at the bottom of the INDEX function docs https://support.google.com/docs/answer/3098242?hl=en it says:

If you set row or column to 0, INDEX returns the array of values for the entire column or row, respectively.

So you can use INDEX in an ARRAYFORMULA as long as you use 0 in the ROW or COL parameter. Then it will populate down.

Upvotes: 0

Ax_
Ax_

Reputation: 997

Use XLOOKUP

=ARRAYFORMULA(XLOOKUP(K2:K,R2:R,S2:S))

With XLOOKUP you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on

Upvotes: 4

Zediiiii
Zediiiii

Reputation: 780

I know this is old now, but it turns out that INDEX() acts as a defacto ARRAYFORMULA() now. You can see a fabulous example of this on this google sheet, which shows how to use a and index(split()) to extract a particular set of text from a cell. The MK.demo tab provides a visual on how the array formula is implied with the INDEX() function.

Nowadays, using a FILTER() or QUERY() function can give the kinds of multiple vlookup the OP was looking for.

Upvotes: 0

EssentialNPC
EssentialNPC

Reputation: 11

In the OP's specific case, one can actually use VLOOKUP for its intended purpose, as a replacement for MATCH:

=arrayformula(if(len(A3:A),VLOOKUP(A3:A,{SheetB!E:E,SheetB!H:H},2,false)))

In the general case of trying to use INDEX to retrieve multiple values, it can be replaced with a kludge of VLOOKUP and SEQUENCE:

=arrayformula(VLOOKUP(A:A,{SEQUENCE(rows(B:B)),B:B},2,true))

does what would have been accomplished by

=arrayformula(INDEX(B:B,A:A))

if the latter worked as OP expected.

Upvotes: 0

user18114472
user18114472

Reputation: 1

im not sure if its gonna work but i did an "IF(ISBLANK() before the INDEX(...) in the ARRAYFORMULA and it went down all the way

Upvotes: -1

Armel Larcier
Armel Larcier

Reputation: 16027

It seems INDEX can not return multiple values. It can not be used inside ARRAYFORMULA.

The only solution I know of is to use VLOOKUP.

See this thread : https://productforums.google.com/forum/#!topic/docs/jVvjbz8u7A8

Example from there :

=ArrayFormula(VLOOKUP( B12:B15; H2:R32; 1; TRUE))

Cheers!

Upvotes: 8

Related Questions