# Thread: INDEX MATCH Formula not working

1. ## INDEX MATCH Formula not working

I am attaching a workbook with two tabs. One is CENTRAL OFFICE and the second is SUBSIDIARY.

i am trying to bring over from SUBSIDIARY to CENTRAL OFFICE certain data using the INDEX MATCH functions, however it is not working.

I would appreciate assistance in correcting the formula or informing me why the CENTRAL OFFICE worksheet cannot pull in the SUBSIDIARY data.

Thanks you

ps: I keep hearing that Index Match is much better than VLOOKUP. So far not so in my opinian.

Thank you.

2. MNN,

Attached is your sheet with the formulas corrected. In the match formula, the lookup array must be only one column not a range of columns.

HTH,
Maud

MNN1.png

3. Maud,

your solution does not 'AUTOMATE" the COLUMN COUNT number of the INDEX formula. In your solution, the COLUMN COUNT Function is hard coded. My readings indicate this could be automated like the ROW COUNT Function.

Am I incorrect on this?

Thank you

4. MNN,

Attached are the formulas you seek. They will automatically adjust the index column based on a horizontal match of the header.

I Converted exclusively from Vlookup to Index/Match. While the comparable speed is debatable, I enjoy the ability to return values from any column while adding rows and columns that do not bust the formulas.

HTH,
Maud

Code:
`Cell F5 =IFERROR(INDEX(SUBSIDIARY!\$A\$7:\$L\$13,MATCH(\$B5,SUBSIDIARY!\$C\$7:\$C\$13,0),MATCH(F\$4,SUBSIDIARY!\$A\$6:\$L\$6,0)),"")`
Copy down then accross

5. Maud,

After playing around with your formula, I was able to automate the COLUMN COUNT function successfully. Now onto the real workbooks.

Thank you so much.

MNN

6. MNN, do you mean something like this (with a "helper" in H2 that could be placed in the formula directly).

Code:
`=IFERROR(INDEX(INDIRECT("SUBSIDIARY!\$A\$7:\$L\$"&\$H\$2),MATCH(\$B5,INDIRECT("SUBSIDIARY!\$C\$7:\$C\$"&\$H\$2),0),1),"")`
This will handle 1000 as the last row in SUBSIDIARY.

7. MNN,

If you want to go more generic with unlimited number of rows, in F5 place the following formula which uses columns as opposed to ranges in columns then copy down. Then copy across.

Consider using the match formula for the header to find the index column. This way, you can change the order of the columns or add a column without adjusting the formulas.

Code:
`=IFERROR(INDEX(SUBSIDIARY!\$A:\$L,MATCH(\$B5,SUBSIDIARY!\$C:\$C,0),MATCH(F\$4,SUBSIDIARY!\$A\$6:\$L\$6,0)),"")`

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•