Forum Discussion

Deleted

Dec 05, 2020

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

excel

Formulas and Functions

SergeiBaklan

Diamond Contributor

Dec 06, 2020

Deleted

Perhaps you mean something like

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

Deleted
Dec 06, 2020
Thank you for the replies. Please see the attached screenshot for reference.
- SergeiBaklan
  Diamond Contributor
  Dec 06, 2020
  Deleted
  
  In theory it shall work, at least on Excel with dynamic arrays.
- Deleted
  Dec 06, 2020
  I am having trouble with showing the balance column
  - Rajesh_Sinha
    Steel Contributor
    Dec 07, 2020
    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.