how to return multiple lookup values with arrayformula?

[edjusted]

I'm trying to "translate" a spreadsheet from Google Sheets to Excel. Basically the spreadsheet has two tabs:
Tab "raw data" contains rows of inventory info. e.g. Item #, Qty on Hand, Qty on Order, Color, and other specs.

Tab 1 is where you type in a list of item #'s, and the arrayformula grabs all of the corresponding info that matches Item # from "raw data".

This works in Google:
=IFERROR(IF(ROW(A2:A)=1,"",VLOOKUP(A2:A,'raw data'!A:M,{3,4,5,6,7,8,9,10,11,12,13},FALSE)),"")
This returns columns 3 to 13 from the "raw data" tab for each row that matches the Item #'s I type in on Tab 1.

In Excel, I had to change it a bit:
{=IFERROR(IF(ROW(A2:A100)=1,"",VLOOKUP(A2:A100,'raw data'!A1:M25000,{3,4,5,6,7,8,9,10,11,12,13},FALSE)),"")}

But it doesn't work correctly. It correctly grabs the matching value from column 3 but then just repeats it across.

For example, if I type in Item # "ABC-001" in tab 1, and the matching data should be:
column 3: on hand = 12 pcs
column 4: on order = 20 pcs
column 5: color = green
etc.

It'll just return 12 12 12, instead of 12 20 green.

Does that make sense? What do I need to do to make this work correctly in Excel?

 

[t0azt]

Are you trying to get all the values in a single cell or are you dragging the formula across?
If you are dragging them accross you can try this:

IFERROR(VLOOKUP($A2,'raw data'!$A$1:$M$25000,COLUMN(C1),FALSE),"")

Anything in column C will return a value of 3 as your column indicator and when you drag accross it will change to column D (4) then E (5) and so on. Hope that helps!

 

[edjusted]

Are you trying to get all the values in a single cell or are you dragging the formula across?
If you are dragging them accross you can try this:

IFERROR(VLOOKUP($A2,'raw data'!$A$1:$M$25000,COLUMN(C1),FALSE),"")

Anything in column C will return a value of 3 as your column indicator and when you drag accross it will change to column D (4) then E (5) and so on. Hope that helps!

Hi, I'm trying to do it as an arrayformula. So in Google, it's actually enclosed with an ARRAYFORMULA command. In Excel, I'm just doing ctrl-enter.

 

[Maartenovich]

I use this in one of my spreadsheets. You could probably translate it into what you need.

{=IFERROR(INDEX(Inkoopfacturen!$D:$D;SMALL(IF($C$4=Inkoopfacturen!$J:$J;ROW(Inkoopfacturen!$J:$J)-ROW(Inkoopfacturen!$J$1)+1);ROW(1:1)));"")}

 

[Fazza]

What about using a non-formula approach? A query table - it is a bit like a pivot table. Just needs a refresh and you have the result.

So Tab1 has the raw data (headers & data under, I guess the headers are in row 1), Tab2 can have the item # header in A1 and the list to be returned under it. The query table results can be from cell B20 - across as many fields as you want & down as many rows as match the item # in column A. The number of rows returned adjusts with the refresh - it can be from zero to whatever. I can spell out the steps if you like, basically start with saving the file and start the wizard with ALT-D-D-N and it takes a minute (when familiar) to set up.

regards

 

[edjusted]

I use this in one of my spreadsheets. You could probably translate it into what you need.

{=IFERROR(INDEX(Inkoopfacturen!$D:$D;SMALL(IF($C$4=Inkoopfacturen!$J:$J;ROW(Inkoopfacturen!$J:$J)-ROW(Inkoopfacturen!$J$1)+1);ROW(1:1)));"")}

Thanks Maartenovich. I'm having trouble getting it to work and I keep getting a "this formula contains an error" error.

If I'm understanding your formula correctly, you're trying to return the contents of column D in tab "Inkoopfacturen" if C4 matches column J of "Inkoopfacturen", right?

So I made some changes and came up with:
=IFERROR(INDEX('Trek inventory'!$A:$M;SMALL(IF($A$2='Trek inventory'!$A:$A;ROW('Trek inventory'!$A:$A)-ROW('Trek inventory'!$A$1)+1);ROW(1:1)));"")

So I want to return columns A through M of tab "Trek inventory" if A2 matches column A in "Trek inventory". But it keeps giving me that error. Any suggestions on what I'm doing wrong?

 

[edjusted]

Hi Fazza, that sounds interesting but it's a bit beyond my knowledge. Thanks for the tip!