Use array formula with index match

Question

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?

Melainemelamed

Answer 1

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!

Knighterrantry

The problem with VLOOKUP is that it returns the nearest value if it couldn't find the right value – Barbour

Not if you pass FALSE as 4th parameter – Knighterrantry

Another problem of VLOOKUP is that it only works rightward, i.e. the data must be on the right side of the search key column. To perform leftward searches, the solution is INDEX MATCH... – Ens

Problem is that VLOOKUP is not case sensitive. – Transcendentalistic

Answer 2

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

Raina

Answer 3

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.

Ikon

Answer 4

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.

Platinize

Answer 5

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.

Cockalorum

Answer 6

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

Avan