How do I autofill a range of cells with values, based on another cell's value? Is there an Excel's equivalent of GoogleSheet's arrayformula?

0
votes

Edit: 1) I use O365 2) Google Formula =ARRAYFORMULA((if(D3=Parameters!B12,Parameters!B13:B20,if(D3=Parameters!C12,Parameters!C13:C20,if(D3=Parameters!D12,Parameters!D13:D20,if(D3=Parameters!E12,Parameters!E13:E20,if(D3=Parameters!F12,Parameters!F13:F20)))))))

I was able to the the thing below in Google Sheets using Arrayformula and nested IFs, but how do I do it in Excel?

Say, A1 is a dropdown, and depending on what the user chooses for A1, I want A3 - A7 to automatically populate.

Examples

If A1=Cat, I want A3, A4, A5 to automatically populate with Tiger, Lion, and Lynx

If A1=K9, I want A3, A4, A5, A6, A7 to automatically populate with Wolf, Fox, Dog, Big Dog, and Good Boi

     |    A    |
1    |   Cat   |
2    |         |
3    |  Tiger  |
4    |  Lion   |
5    |  Lynx   |
6    |         |
7    |         |


     |    A    |
1    |    K9   |
2    |         |
3    |  Wolf   |
4    |  Fox    |
5    |  Dog    |
6    | Big Dog |
7    | Good Boi|

How can I do this in Excel?

1
excelvbaexcel-formulaarray-formulas
Please post the google formula you are using. – Scott Craner
Excel is different than Google. It requires a formula in every cell that returns a value. You need to highlight A3:A7 with A3 active. Put just your IF function in the formula bar and hit Ctrl-Shift-Enter and Excel will put the formula in the rest of the cells and the output should be correct. – Scott Craner
Are the items in the list hard coded in the formula or do they pull from another table? – Scott Craner
@ScottCraner if OP is using the latest O365 and can use dynamic arrays (somewhat unlikely though), "It requires a formula in every cell that returns a value" is no longer true. – Mathieu Guindon
I was unaware of those, cool something new to learn. @MathieuGuindon – Scott Craner

1 Answers

2
votes

The technique depends on which version of Excel you have.

Excel without the new Dynamic Array formulas will require you to pre-fill the cells with a formula that looks up the value.

With the new Dynamic Arrays (currently only available in Office 365 Insider), you can have just one formula in A3 and it will spill down automatically. In the screenshot, the formula is only in A3, nothing has been copied down.

enter image description here