Index and Match

[Deleted]

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,

[#2]

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!

[#3]

@Deleted 

Perhaps you mean something like

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

[#4]

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

[#5]

I am having trouble with showing the balance column

[#6]

@Deleted 

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

[#7]

@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.

[#8]

@Rajesh_Sinha 

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

[#9]

@Sergei Baklan 

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.

[#10]

@Rajesh_Sinha 

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. 

[#11]

@Sergei Baklan 

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 !

[#12]

@Rajesh_Sinha 

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

[#13]

@Sergei Baklan 

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

[#14]

@Rajesh_Sinha 

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. 

[#15]

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