Search code examples
excelexcel-formulaexcel-2007vlookup

Excel vlookup table array

Please forgive me if the formula isn´t correctly translated as my Excel is in Spanish.

=IF.ERROR(VLOOKUP(B3;[17.01.2017.xlsx]Hoja1!$A$2:$B$29;2;FALSE);"")

What I need is to get the date from the active workbook where the formula is and have the rest remain the same where its getting the infomration from a different workbook.

Woorkbook1:

enter image description here

Workbook2:

enter image description here

The formula is where you can see 21.481,65. This formula is getting this information from another workbook named 17.01.2017.xlsx.

I want the date to be the same as the date seen in the image on the left.

Solution

  • You can use INDIRECT to reference a different sheet.

    =IFERROR(VLOOKUP(B$3, INDIRECT("'[" & $A4 & ".xlsx]" & "Hoja1'!$A$2:$B$29" ), 2, FALSE), "")

    This is the same as your formula, but instead of [17.01.2017.xlsx]Hoja1!$A$2:$B$29 it is replaced with INDIRECT("'[" & $A4 & ".xlsx]" & "Hoja1'!$A$2:$B$29" ).

    You might have to change the formatting to fit with the Spanish Excel, and also make sure the file extension is correct .xls, .xlsx, .xlsm etc.

    This will only work if all files are open, also I don't think you can save a file with / so your dates would have to be with a . instead.