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?
Add a comment
|
6 Answers
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!
4 Comments
Hernan Rajchert
The problem with VLOOKUP is that it returns the nearest value if it couldn't find the right value
Armel Larcier
Not if you pass FALSE as 4th parameter
alleen1
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...
Kristjan Adojaan
Problem is that VLOOKUP is not case sensitive.
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.
Comments
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.
Comments
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.
Comments
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
