Using Index and Match with Indirect in Excel

0
votes

I need some help writing a formula. Essentially what I want to do is be able to find a specific value from another workbook (Workbook2) by using the Index and Match functions. However, the workbook being referenced can change (say, one day I need to use Workbook 3). I am using Indirect to call to that other workbook. What I have right now is

 =INDEX(INDIRECT(SHEET1!$D$8),MATCH(A6,INDIRECT(SHEET1!$D$8),0),MATCH(N1,INDIRECT(SHEET1!$D$8),0))

where the SHEET1$D$8 cell holds the path to the other workbook (either Workbook2 or Workbook3) and A6 and N1 refer to the specific row and column that I want to match (for instance, A6 = "Type" and N1 = 713)

My issue though, is that after evaluating the function, it appears that when it goes to match, it can find the other workbook, but can't find the value (even though the specified row and column are definitely present). What I currently get is a #N/A value.

So, anyone know what might be going on? Or if there's a better way to approach this?

Thanks

1
excelindexingexcel-formulaexcel-indirect
Is there more than one sheet in Workbook 2? If yes, does the cell Sheet1!$D$8 include the sheet name you want to target? It may be helpful to see sample data. – cybernetic.nomad
What happens if you try the index/match formula in the target worksheet without messing with the indirect bit? If that works then you know it's related to Indirect. – guitarthrower

1 Answers

0
votes

thanks for the responses. Turns out I just had to limit the cell range for matching the other workbook (not sure why, but it didn't seem to like it when I searched the entire worksheet).