Index & Match with an Array

[Carly]

I know there is a way of typring an index & match formula into one columns & then putting it into as many columns as you want by using an array but I can't quite get it to work. Basically I am trying to match back 9 columns from one spreadsheet onto another & I have the following formula which I am setting in the 9 columns by using CTRL + SHIFT + ENTER but when I drag the formula down it only matches the 9 columns of the header row & nothing else.

I know I must be missing something simple but not sure what that is, any help?

{=INDEX('[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$J$44,MATCH(C1,'[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!A1,0),0)}

Kind Regards

Carly

 

[scottylad2]

I know there is a way of typring an index & match formula into one columns & then putting it into as many columns as you want by using an array but I can't quite get it to work. Basically I am trying to match back 9 columns from one spreadsheet onto another & I have the following formula which I am setting in the 9 columns by using CTRL + SHIFT + ENTER but when I drag the formula down it only matches the 9 columns of the header row & nothing else.

I know I must be missing something simple but not sure what that is, any help?

{=INDEX('[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$J$44,MATCH(C1,'[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!A1,0),0)}

Kind Regards

Carly

you don#t need a Ctrl+Shift+Enter for index and Match

MATCH(C1,'[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!A1,0) This part of your Syntax used to select the Row number for the Index will result in 1, is that correct? Your looking up a value in C1, but only comparing it with a single cell A1 which will result in 1 if theres a match or #NA if there's none....atn should be say A1:A10 and the match will return the Ordinal position of your lookup value, if your array only contains 1 cell ref (A1) then thats all it will find


If you want to add a formula to a lot of cells at the same time, highlight them all, type your formula and while holding Ctrl hit the Enter button, not Ctrl + Shift + Enter

 

[Carly]

I thought I needed CTRL + SHIFT + ENTER to control the array, so that I could do the 9 columns all at once?

I have now changed the formula to:

=INDEX('[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$J$44,MATCH(C1,'[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$A$44,0),0)

and it works great across all 9 columns I know I was missing something simple...thanks for your help.

 

[scottylad2]

I thought I needed CTRL + SHIFT + ENTER to control the array, so that I could do the 9 columns all at once?

Ctrl + Enter is sufficient to for entering Formula into lots of cells at the same times.

Ctrl + Shift + Enter is used to enter an Array formula, which is slightly different and not required for what you want.

....thanks for your help.

you're welcome

 

[Carly]

I do want the array though as when I don't put it there, the formula's don't work. I guess it's because without the array it doesn't know what column I need it to return.

 

[scottylad2]

within the Match function one of the arguments is Lookup Array, and within Index there 1st argument is Array. While the reference to the word Array is correct, it's not an Array formula that needs entered with Ctrl + Shift + Enter, there is specific types of formula that need entered in that fashion and will show the {} in the formula bar, but you shouldn't require that for what your formulas showing. It's best avoided if you don't require it, it can add to calculation times.


when doing an index/match formula you can enter that by usng Enter. If your entering the formula into several cells, use Ctrl + Enter.

 

[Carly]

I totally appreciate the explanation, I guess what I don't understand is that I get what I want when using the array {} but don't when I don't.

I do want to return 9 different columns which would mean entering a different formula in each column

For Example:

1st column: =INDEX('[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$J$44,MATCH(C1,'[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$A$44,0),2)

2nd column: =INDEX('[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$J$44,MATCH(C1,'[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$A$44,0),3)

,etc.

 

[scottylad2]

For Example:

1st column: =INDEX('[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$J$44,MATCH(C1,'[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$A$44,0),COLUMNS($B1:C1))

2nd column: =INDEX('[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$J$44,MATCH(C1,'[Trade Counters plus Branch Physical Addresses.xlsx]BranchPhysicalAddresses'!$A$1:$A$44,0),COLUMNS($B1:D1))

,etc.

if you re[;ace 2 ,3 etc with the Colums Function, as the formulas goes across the sheet the column number will increment by 1. You only have to enter it into the first formula, it will automatically increment thereafter