0

I have a bunch of .csv files that have IDs that I am trying to insert into a formula. The IDs are all listed in column F. Here is F in my sheet:

F
701
684
128
701
485
701
...

Each CSV file is in a folder and titled the exact contents of F plus .csv so "701.csv" "684.csv" etc. Here is the formula I am trying but is not working:

=VLOOKUP(D1, F1 & '.csv'!$B:$C,2)

This formula works fine if I replace the "F1 & '.csv'" portion with just the ID like so:

=VLOOKUP(D1, '701.csv'!$B:$C,2)

But I don't want to have to manually enter the ID for every single row (there's hundreds of rows).

Any help would be greatly appreciated!

Improve this question
4
  • 1
    You are missing double and single quotations. It should be something like "'"&F1&".csv'!$B:$C"
    – gns100
    Commented Sep 19, 2022 at 17:44
  • Not sure what I did wrong but still get an error using: =VLOOKUP(D2,"'"&F2&".csv'!$B:$C",2). Also tried doing the full directory and still error: =VLOOKUP(D2,"'C:\Users\person\Desktop\ub\CF\AS\["&F2&".csv]"&F2&"'!$B:$C",2)
    – user2406786
    Commented Sep 19, 2022 at 17:55
  • not sure why your hardcoded one works, seems like you may also be missing the sheet name in there. But maybe excel doesn't need sheet names when working with .csv files.
    – gns100
    Commented Sep 19, 2022 at 18:58
  • @gns100 If you are building a reference by assembling text + cell references, you have to use the INDIRECT function. See my answer below.
    – Max R
    Commented Sep 22, 2022 at 3:25

1 Answer 1

Reset to default
1

I believe you are going to need an INDIRECT function call to get Excel to understand what you are trying to do.

=VLOOKUP(D1,INDIRECT("'"&F1&".csv'!$B:$C"),2)

If you use a formula to derive the text string, rather than a static string, you will usually have to use INDIRECT(). This includes any use of concatenation.

Be aware that INDIRECT is a volatile function, meaning it is recalculated with every change in the worksheet, and can cause sluggish performance in large sheets.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .