Deleted
Not applicable

# Index and Match

Hello there,

I have a large file of data and I have a set of random values from a different excel workbook that I am trying to match to my large file. For example, lets say I have four columns A,B, C, & D in my large file. I am trying to match the random values I have from the other excel workbook to column B, If an exact match was found, I want to return the values from columns A and D along with column B. However, I am having trouble with writing this formula. so the new formatting would return in a table and look like column B first then Column A, then Column D in a table.

Thank you,

14 Replies

# Re: Index and Match

Please edit your post and share some sample data with us,, and what you are trying to match the value,,, along with the formula you have tried so far!

# Re: Index and Match

@Deleted

Perhaps you mean something like

``=INDEX(A:D,MATCH(value,B:B,0),{1,2,4})``

# Re: Index and Match

Thank you for the replies. Please see the attached screenshot for reference.

# Re: Index and Match

I am having trouble with showing the balance column

# Re: Index and Match

@Deleted

In theory it shall work, at least on Excel with dynamic arrays.

# Re: Index and Match

@Deleted

• You need to write this formula in cell C2:

``=INDEX([Workbook.xls]Sheet1!\$F\$2:\$F\$10,MATCH(A2,[Workbook.xls]Sheet1!\$b\$2:\$b\$10,0))``

You get related data in column C .

Note:

• Your formula is not working because you have used invalid data range with, MATCH(A2:A10, it should like MATCH(A2
• You need to adjust cell references in the formula as needed.

# Re: Index and Match

That returns only one row and all columns. Initial formula returns all rows and two columns at once.

# Re: Index and Match

No this returns match value from one column only, since Index is on Column F.

``=INDEX([Workbook.xls]Sheet1!\$F\$2:\$F\$10,MATCH(A2,[Workbook.xls]Sheet1!\$b\$2:\$b\$10,0))``

To get values from multiple columns this needs to modified:

``=INDEX([Workbook.xls]Sheet1!\$A\$2:\$F\$10,MATCH(A2,[Workbook.xls]Sheet1!\$b\$2:\$b\$10,0),{1,2,4})``

For multiple Rows an Array (CSE) formula is required with SMALL function.

``{=INDEX(\$A\$1:\$D\$25, SMALL(IF(COUNTIF(\$G\$1, \$B\$1:\$B\$25), ROW(\$A\$1:\$D\$25)-MIN(ROW(\$A\$1:\$D\$25))+1), ROW(A1)), COLUMN(A1))}``

• Finish formula with Ctrl+Shift+Enter and fill across.
• Adjust cell references in formula as needed.

# Re: Index and Match

That's what in initial formula, it returns columns {1,6} and it's not clear why second one doesn't work. Multiple rows in initial formula are for dynamic array case as MATCH(range1, reange2, 0), but in this case that's most probably doesn't matter.

# Re: Index and Match

Considering the last post my 1st formula returns value form column F only. The second one returns from column A,  B, and D only,,  the last one returns multiple records if match with the criteria, and all are working !

# Re: Index and Match

I have no doubt it works in your case, the question is why it doesn't work for @Deleted

# Re: Index and Match

Check the Note part of my first post, I've shown the reason why OP's formula is not working!

# Re: Index and Match

MATCH(A2:A10,.. shall work. On Excel with dynamic arrays it returns an array, on old Excel implicit intersection shall be silently switched on and single value will be taken.

# Re: Index and Match

Neither the OP nor I've suggested an array formula, being solution provider sometimes need to consider OP understanding !!