Excel vlookup table array

Question

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:

Workbook2:

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.

Get the date from where in the active workbook? Can you add an example of your data in both workbooks, and the desired outcome? — Tim Wilkinson
– Tim Wilkinson, Commented Jan 19, 2017 at 10:57

Tim Wilkinson · Accepted Answer · 2017-01-19 11:20:59Z

1

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.

answered Jan 19, 2017 at 11:20

Tim Wilkinson

3,80111 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel vlookup table array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related